Forum Discussion
How can I create multiple dependent drop down lists
Lycias I realize this is an old discussion, but in case you or anyone else is interested in trying a LAMBDA based solution for creating dynamic, multi-level dependent drop-down lists, please see the attached workbook...
Open Name Manager to view the custom LAMBDA functions and named ranges. Basically, the TBL.TRANSFORM and TBL.JOINCOLS functions are used to transform your national areas table into a single lookup range with three columns: List_ID, Parent_ID and List_Items. The Parent_ID for each List_Item is created by joining all of the previous items for each record with a delimiter. These two named ranges are then passed to the third function, GET.LIST, which is used directly as the data validation list source.
The first data validation level (non-dependent) uses the first two arguments only:
=GET.LIST(List_Items, Parent_ID)
All other dependent data validation levels can be set together using the same syntax, with a mixed cell reference for the [criteria] argument. For example:
=GET.LIST(List_Items, Parent_ID, $A2:A2, " | ", No_Match)
For reference, the three custom LAMBDA functions are defined as follows:
TBL.TRANSFORM:
=LAMBDA(array,delimiter,[if_empty],
SORT(DROP(REDUCE(0, SEQUENCE(COLUMNS(array)), LAMBDA(v,n, LET(
a, UNIQUE(CHOOSECOLS(array, SEQUENCE(n))),
b, FILTER(a, TAKE(a,, -1)<>0, if_empty),
h, ROWS(b),
VSTACK(v, HSTACK(EXPAND(n, h,, n), IF(n=1, EXPAND("φ", h,, "φ"),
TBL.JOINCOLS(TAKE(b,, n-1), delimiter)), TAKE(b,, -1)))))), 1), {1,2,3}))
TBL.JOINCOLS:
=LAMBDA(array,delimiter,
TEXTAFTER(REDUCE("", SEQUENCE(COLUMNS(array)), LAMBDA(v,n,
v&delimiter&CHOOSECOLS(array, n))), delimiter))
GET.LIST:
=LAMBDA(list_range,criteria_range,[criteria],[delimiter],[if_empty], LET(
v, IF(ISOMITTED(criteria), "φ", TEXTJOIN(delimiter, 0, criteria)),
XLOOKUP(v, criteria_range, list_range, if_empty):
XLOOKUP(v, criteria_range, list_range, if_empty,, -1)))
These functions are dynamic and can transform a table of any dimensions. Please see the attached workbook for a complete demonstration...