SOLVED

Source data for dropdown validation list

Occasional Contributor

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.

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

Hi @george1685 

 

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

Screenshot.png

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

 

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