Forum Discussion
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 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.
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!
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!
