This article will explain how to create drop-down list depending on each other from a table without extracting first
Table with hierarchical data
We will start from a table where the data is arranged in a hierarchical way. The data does not need to be sorted.
With this data, we will build a depending drop-down list. You can see in this picture that only the beverage sub-category is enabled in the drop-down list.
To achieve this work, we will use the dynamic array functions that are present in Microsoft 365 or Excel Online only.
Creation of the first drop-down menu
For the first drop-down list, the one corresponding to the highest hierarchical level, we will extract the unique values of the column A. For this, we will use the SINGLE function.
=UNIQUE(A2:A56)
And we can also embed this formula into the function SORT to order the list of products
Next, we will create a drop-down list in the menu Data> Data validation
Choose the List option and write the following function
=$E$2#
The # sign corresponds to the extended reference of the array formula.
With this trick, whatever the number of values returned by the array formula UNIQUE, the drop-down list will always displayed all the values.
Create the dependent drop-down list
Now we're going to build a new formula based on the result of the previous selection. This time, we will use the FILTER function.
Because the FILTER function extract values in function of a condition. The condition will be the category selected 😉
To write the formula, we will
- Indicate the column that contains the data to return
- Indicate the column on which to filter
- the test to perform (here =)
- the cell containing the test value (here cell I3)
=FILTER(B2:B56,A2:A56=I3,"")
Of course, we only need to keep unique values. And again we will use the UNIQUE function
=UNIQUE(FILTER(B2:B56,A2:A56=I3,""))
And to make things perfect, we will also sort the data
=SORT(UNIQUE(FILTER(B2:B56,A2:A56=I3,"")))
And as we did for the first drop-down menu, we'll again use the extended references to design the second drop-down menu with the result of the second formula.
=$F$2#
And with this new function we have created a depending drop-down list 😀👍
