Forum Discussion

Lycias's avatar
Lycias
Brass Contributor
May 23, 2023

How can I create multiple dependent drop down lists

I want to create multiple dependent drop-down lists from the file below. When I select a country in column B I want only entries from column D for that country to appear in the next drop-down list, a...
excel
djclements's avatar
djclements
Silver Contributor
Jan 25, 2024

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

Resources