SOLVED

Source data for dropdown validation list

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3141755%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ESource%20data%20for%20dropdown%20validation%20list%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3141755%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20contents%20of%20an%20EXCEL%20cell%2C%26nbsp%3B%20as%20the%20source%26nbsp%3B%20in%20a%20dropdown%20validation%20list%20for%20another%20cell.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ELet's%20say%20for%20example%20the%20cell%20E3%20contains%20the%20string%3A%20%22DN%3BPN%3BCompount%3BWall%20thickness%22%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20the%20contents%20of%20the%20cell%20F3%20to%20be%20validated%20with%20the%20following%20options%3A%20DN%2C%20PN%2C%20Compound%20and%20Wall%20thickness%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EAny%20tip%20will%20greatly%20appreciated.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3141755%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3141755%22%20slang%3D%22en-US%22%3ESource%20data%20for%20dropdown%20validation%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3141755%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20contents%20of%20an%20EXCEL%20cell%2C%26nbsp%3B%20as%20the%20source%26nbsp%3B%20in%20a%20dropdown%20validation%20list%20for%20another%20cell.%3C%2FP%3E%3CP%3ELet's%20say%20for%20example%20the%20cell%20E3%20contains%20the%20string%3A%20%22DN%3BPN%3BCompount%3BWall%20thickness%22%3C%2FP%3E%3CP%3EIs%20it%20possible%20the%20contents%20of%20the%20cell%20F3%20to%20be%20validated%20with%20the%20following%20options%3A%20DN%2C%20PN%2C%20Compound%20and%20Wall%20thickness%3C%2FP%3E%3CP%3EAny%20tip%20will%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3141755%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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