Cascading / Multi-Dependent Dropdown Lists

Copper Contributor

Considerations:

    There are three (3) dropdown items to be used; entity, expense category & expense sub-category.

    There are about 20 categories and 40 sub-categories.

    No one entity has all 20 categories and no one entity has all 40 sub-categories.

    By having dependent drop-down lists, it will the user to only see & pick from the actual numbers of categories & sub-categories that the specific entity has and not picking from the two larger overall lists of 20 categories & 40 sub-categories.

 

Goal:

    Build the functionality for the seven (7) entities where only the expense categories that each entity would have are shown in the dropdown list associated with that respective entity, i.e., entity A might only have seven (7) kinds of expenses categories out of the overall list of 20, while entity B might have 12. AND, for each expense category, there may be between 0 to nine (9) expense sub-categories.

    Examples might look like this:

        Entity A has seven (7) kinds of expense categories where some of those seven (7) have no sub-category but some of the expense categories may have between one to nine sub-expenses categories.

        Entity B has 12 kinds of expense categories where some of those 12 have no sub-category but some of the expense categories may have between one to nine sub-expenses categories.

 

This would allow the user to see & pick thru smaller, more efficient lists of of data. For example, if picking entity A to attribute an expense to, the drop down list of expense category might only show seven (7) categories and not the full list of 20 different expense categories, Then the second drop-down list of expense sub-categories might be as small as zero (0) or as many as nine (9) but not the full list of 40.

 

Drop Down List for Taxes 

2 Replies

@Pete_Blackwell 

Set the source for the data validation validation in G2 to

=INDIRECT("Tbl_"&$F2)

And the source for the data validation drop down in H2 to

=INDIRECT("Tbl_"&$G2)

S2178.png

 

S2177.png

Thank you!