Forum Discussion
christine1314
Dec 14, 2023Copper Contributor
How to create a cascading dynamic dropdown list
I am trying to create a dynamic dropdown list (which I can do), but then cascade/apply it to multiple rows (can't do). I have a source table with 3 columns of data column C- business unit, co...
djclements
Dec 15, 2023Bronze Contributor
christine1314 One method you could try is to use OFFSET with MATCH and COUNTIF. Select the range where you want to apply data validation (ie: N5:O100), then use the following formula as the data validation list source:
=OFFSET(D$4, MATCH(M5, C$5:C$46, 0), 0, COUNTIF(C$5:C$46, M5))
With MS365, another option is to define a custom LAMBDA function in name manager called XLIST with the following formula:
=LAMBDA(lookup_value,lookup_array,return_array,
XLOOKUP(lookup_value, lookup_array, return_array):XLOOKUP(lookup_value, lookup_array, return_array,,, -1))
Once defined, the XLIST function can then be used as the data validation list source:
=XLIST(M5, C$5:C$46, D$5:D$46)
NOTE: When creating the data validation list, you may be prompted with "The Source currently evaluates to an error. Do you want to continue?" If so, click "Yes" to proceed.
Please see the attached workbook for an example of how this can be setup...
EDIT: Also worth mentioning, the source data must be arranged in order by business unit, then by establishment. If the data is out of order, the above-mentioned methods will return inaccurate results.
christine1314
Dec 17, 2023Copper Contributor
When you say 'the source data must be arranged in order by business unit, then by establishment', do you mean alphabetical order, or column order?