In worksheet, we can create dynamic drop down lists with the Data Validation feature and the Indirect function.
In sample sheet,we have a table of five columns that indicate five types of foodstuff: fruit, food, meat,vegetable and drink and below them are the specific food name :
We need to create one drop down list that contains the foodstuff, such as fruit, food, vegetable,meat and drink . The second drop-down would have the specific food name. If we select meat item from first drop-down, the second drop-down will show beef, mutton, chicken, port, fish and veal.
To do this, please apply the following steps:
- First, we need to create range names for these columns and the first categories row.
- Let's create a range name for the categories,for this the first row, we selected the A1:E1, and typed the range name Foodstuff into the Name Box, then pressed Enter key.
- Then we need to name the range for each of the columns as shown below:
- Now we can create the first drop down list, we selected a blank cell or a column that we want to apply this drop down list (I5 cell is selected), and then we clicked Data > Data Validation .In the Data Validation dialog box, we clicked Settings tab, we chose List from the Allow drop down list, and entered this formula =Foodstuff into the Source box.
Our first drop down list have been created.
- Then we can create the second drop down list,we selected J5 cell, and click Data > Data Validation again, in the Data Validation dialog box, we clicked Settings tab, we chose List from the Allow drop down list, and entered this formula =indirect($I$5) into the Source box.
Our dependent drop down list have been created successfully.
If user choose one type of the foodstuff, the corresponding cell will only display its specific food name.
