Dependent Dropdown Lists in Excel

This example describes how to create dependent dropdown lists in Excel. Here’s what we’re trying to achieve:

When the user selects “Pizza” from the first dropdown…

… the second drop-down list will contain the dishes of this section. In our case, this is a list of possible pizzas.

Translator’s Note: Let’s call the sheet on which the linked drop-down lists will be created Sheet1, and the auxiliary sheet – Sheet2.

To create such dependent dropdown lists, follow our instructions:

1. Create the following named ranges on a worksheet Sheet2:
   

   Range Name	Address range
   Food	A1: A3
   Pizza	V1:V4
   Pancakes	S1:S2
   Chinese	D1: D3

2. Highlight a cell B1 on the sheet Sheet1.
3. On the Advanced tab Data (data) click Data Validation (Data validation).A dialog box will open Data Validation (Checking input values).
4. Выберите List (List) from the drop-down list Allow (data type).
5. Place the cursor in the field Source (Source) and enter “=Food”.
6. Press OK.

Result:

7. Now select a cell E1.
8. Выберите List (List) from drop down list Allow (data type).
9. Place the cursor in the field Source (source) and enter:

   =INDIRECT($B$1)

   =ДВССЫЛ($B$1)

10. Press OK.

Result:

Explanation: Function INDIRECT (INDIRECT) returns the link specified by the text value. For example, the user selects “Chinese” from the first dropdown list, and the function INDIRECT (INDIRECT) returns a reference to a named range Chinesewhich is on the sheet Sheet2. As a result, the second drop-down list consists of Chinese dishes.