How to create a cascading dynamic dropdown list

[christine1314]

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,
• column D -establishment,
• column E -department. 

I have set up a preparation table to create the necessary dropdowns:

1. In cell G4, return the unique values for column C (business unit). formula=UNIQUE(C5:C46)

2. In cell H4, use a filter function to return all the establishments for the business unit selected in M5. formula: =FILTER(D5:D46,C5:C46=M5)

3. In cell I4, use a filter function to return all the departments for the establishment selected in N5. formula: =FILTER(E5:E46,D5:D46=N5)

This works perfectly for the first row, but I can't apply it to subsequent rows because of the reference to M5 or N5. Wondering how I can make that cell reference relative. I haven't been able to work out how to use the OFFSET function or anything else.  

[#2]

@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.

[#3]

@djclements Thank you. We've been able to make this work by putting it in the data validation for each cell, however, as we could be entering up to 100 rows of data, we aren't keen to have to replicate this data validation formula 200 times. Any way it can be referenced once in H4 and I4 to avoid having to enter the formula 200 times? That way the data validation can just reference the list that the formula creates.

[#4]

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?

[#5]

https://techcommunity.microsoft.com/t5/excel/dropdown-within-a-dropdown/m-p/3954747

[#6]

@christine1314 You just need to get the cell references correct by using a relative reference on the lookup_value and an absolute reference on the lookup_range and return_range. If you are unfamiliar with these terms, an absolute reference uses dollar signs ($) to "lock" or "fix" the row and/or column so that no matter where you copy the formula to, the reference is always the referring to the same cell/range, whereas a relative reference (without dollar signs) is always "relative" to the current cell.

For example, with the XLIST method I mentioned, I selected the range N5:O10, then went to Data Validation and input the following formula ONCE to apply it to the entire range:

=XLIST(M5, C$5:C$24, D$5:D$24)

The lookup_value M5 is relative to the current cell (which is N5) and will automatically adjust as you move down the column (M6 for N6, M7 for N7, etc.). The lookup_range C$5:C$24 and return_range D$5:D$24 are using mixed references with "fixed" rows and relative columns... this means that as you move down column N, it will still be referencing the same ranges; BUT, for column O, the references shift over one column. For example, the formula in cell O10 will automatically adjust to =XLIST(N10, D$5:D$24, E$5:E$24).

Data Validation with Mixed Cell References

Regarding my comment about arranging the source data in order by business unit, then by establishment, I meant the data for the picklists needs to be sorted (or grouped) by the first two columns. Since the formulas are basically finding the first match in the list and the last match in the list, then returning everything in between, if you have options listed for "Business Unit 1" followed by options for "Business Unit 2", then more options for "Business Unit 1" below that, the formula will return everything between the first and last occurrence of "Business Unit 1", which will include all of "Business Unit 2". I hope that makes sense. If you run into any other issues, please let me know (and perhaps share some examples of the picklist options you are using for business unit, establishment and department). Cheers!

[#7]

@djclements Thank you, that's great. We've been able to use the single formula now and apply it to the whole column. Thanks for all your help. I will reach out if there is anything else. Cheers!