Create Dependent Drop-down Lists
The dependent drop-down lists in Excel is designed to create conditional drop-down lists. Specifically, selecting an item from a drop-down list will change the available items in another drop-down list. Let’s create one by following the steps below.
1. In Sheet1 create four named ranges. (a) state [B4:B6], (b) texas [C4:C7], (c) nevada [D4:D6], and (d) florida [E4:E7].
![create named ranges](https://overallmoney.com/wp-content/uploads/2022/03/dependent-drop-down-1.png)
2. In Sheet2, select cell C2.
3. From the Data tab, click “Data Validation”.
![click data validation](https://overallmoney.com/wp-content/uploads/2022/03/validation-2.png)
4. From the Settings tab of the Data Validation dialog box, select “List” in Allow box. In the source box, type =state.
![data validation criteria](https://overallmoney.com/wp-content/uploads/2022/03/dependent-drop-down-2.png)
5. Click OK and you get the drop-down list shown below.
![drop-down list](https://overallmoney.com/wp-content/uploads/2022/03/dependent-drop-down-3.png)
6. Now, select cell F2.
7. From the Settings tab of the Data Validation dialog box, select “List” in Allow box. In the source box, type =INDIRECT($C$2).
![INDIRECT function](https://overallmoney.com/wp-content/uploads/2022/03/dependent-drop-down-4.png)
8. Click OK. You find the result below.
![dependent drop-down lists](https://overallmoney.com/wp-content/uploads/2022/03/dependent-drop-down-5.png)
Note: The INDIRECT function shows the list in the second drop-down list based on the item selected in the first drop-down list.
If you like to download the Excel file containing the example above, click HERE.
6 of 6 finished! Congrats!! You can now move on to Next Chapter 09: Next Example >> |
<< Previous Example | Skip to Next Chapter 09: Using Templates |