Source data for dropdown validation list

[george1685]

Hi,

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.

[#2 · Accepted solution]

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:

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

Corresponding sample attached

[#3]

Hi @L z.
Yes, I run EXCEL 2021 and it works.
Thank you very much.

[#4]

Glad I could help & Thanks for providing feedback