In Excel, to create dependent dropdown list, I will convert the dropdown list data to an Excel table and use the INDIRECT function in the data validation feature. INDIRECT returns a valid cell reference from the text string.
The following dataset contains two columns: Type and Item, both containing dropdown list. Now I want a dependent dropdown list that can show options depending on the selection from the first dropdown list, like shown below:
To accomplish the task, I perform the following steps:
I enter the options for the first dropdown in the first most cell of column A and B. For the second, I enter the options below them, like so:
I convert both the dropdown data to Excel tables separately and name each one of them, like so:
After this, I name the column header Type in column D for the first dropdown and Item in column E for the second dropdown. I convert both of them to an Excel table.
Then, I use data validation using the shortcut [Alt A V Enter] in sequence to create the first dropdown in D2, listing food,chocolate, like so:
After this, I create the second dropdown in E2 using the INDIRECT formula, like shown below:
To start a new row, press the Tab key. Your dependent dropdown list using INDIRECT is ready! I hope this post helped you.
