Dependent DropDown List

[msharma864512]

I am trying to create dependent dropdown list. So that If i select one item from the first drop down list, other dropdowns should only have items which are related to first drop down list.

Details are in the attached spreadsheet.

Regards,
Manish

 

[pecoflyer]

See https://www.contextures.com/xldataval02.html

 

[msharma864512]

See https://www.contextures.com/xldataval02.html

I have tried leveraging this.. But I was unable to apply the same in my dataset

 

[djc]

What version of Excel are you using? There are newer methods available if you have Excel for Microsoft 365.

 

[msharma864512]

I

What version of Excel are you using? There are newer methods available if you have Excel for Microsoft 365.

using Microsoft 365

 

[djc]

using Microsoft 365

Great! Attached is LAMBDA based method for MS365. Take a look in Name Manager. There are 3 custom functions defined:

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

TBL.TRANSFORM (with TBL.JOINCOLS) is used to transform the dependent list table into a 3-column spilled range, consisting of the List_ID, Parent_ID and List_Items.

There are also named ranges defined for both Parent_ID and List_Items, as well as a single cell named No_Match, which can be used to specify a default option for the [if_empty] argument of GET.LIST (because a range is required to make the XLOOKUP:XLOOKUP reference work).

GET.LIST is used as the data validation list source.

Conditional Formatting was also used to highlight dependent selections that no longer exist after a parent selection has been changed.

Check it out and see if you can make sense of how it was setup.

If you want to explore additional options for MS365, there are various videos available on YouTube. Search "access analytic multi level drop down list" and check out his 2 videos on the topic.