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.
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
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)
Of course, we only need to keep unique values. And again we will use the UNIQUE function
And to make things perfect, we will also sort the data
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.
And with this new function we have created a depending drop-down list 😀👍