Source data for dropdown validation list

Occasional Contributor


I am trying to use the contents of an EXCEL cell,  as the source  in a dropdown validation list for another cell.

Let's say for example the cell E3 contains the string: "DN;PN;Compount;Wall thickness"

Is it possible the contents of the cell F3 to be validated with the following options: DN, PN, Compound and Wall thickness

Any tip will greatly appreciated.

3 Replies
best response confirmed by Hans Vogelaar (MVP)

Hi @george1685 


Assuming your run Excel Web/2021/365 a possible workaround with an 'Helper Sheet' (can be hidden)


Data Validation formula in Sheet1!F3: ='Helper Sheet'!B3#

In 'Helper Sheet'!B3:

  StringAddress, Sheet1!E3,
  Delim,         ";",
  SeqLen,        SEQUENCE( LEN(StringAddress)+1 ),
      DelimString, Delim & StringAddress,
      SplitString, MID(DelimString, SeqLen, 1),
      arr,        (SplitString = Delim) * SeqLen,
      FILTER(arr, arr > 0)
      StringDelim, StringAddress & Delim,
      SplitString, MID(StringDelim, SeqLen, 1),
      arr,        (SplitString = Delim) * SeqLen,
      FILTER(arr, arr > 0)
  MID(StringAddress, arrStart, arrEnd - arrStart)

Corresponding sample attached


Hi @L z.
Yes, I run EXCEL 2021 and it works.
Thank you very much.
Glad I could help & Thanks for providing feedback