Feb 09 2022 12:48 AM
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.
Feb 09 2022 01:59 AM
SolutionHi @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
Feb 09 2022 08:40 AM
Feb 09 2022 09:40 AM
Feb 09 2022 01:59 AM
SolutionHi @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