How to Create Dynamic Dependent Drop-Down Lists in Excel Using the INDIRECT Function

Data validation drop-down lists are essential for keeping Excel spreadsheets organized and error-free. However, when dealing with categorized data—such as selecting a “Country” in one cell and only showing that country’s “Cities” in the next cell—you need a dependent drop-down list. This is achieved by combining Excel’s Named Ranges with the powerful INDIRECT function.

How the INDIRECT Function Works

The INDIRECT function takes a text string and converts it into a valid cell reference or Named Range. When used inside Data Validation, it allows the secondary drop-down list to dynamically look up the list of items based on the text selected in the primary drop-down list.

Step 1: Set Up and Name Your Data Ranges

Before creating the drop-downs, you must define the lists using Named Ranges.

1. On a new worksheet, type your primary categories in row 1 (e.g., cell A1: “USA”, cell B1: “Canada”).
2. Below each header, list the dependent items (e.g., A2: “New York”, A3: “Los Angeles”, B2: “Toronto”, B3: “Vancouver”).
3. Select the primary categories (A1:B1). Go to the Formulas tab, click Define Name, and name this range Countries.
4. Select the items under USA (A2:A3). Click Define Name and name it exactly USA.
5. Select the items under Canada (B2:B3). Click Define Name and name it exactly Canada.

Crucial Note: Named Ranges cannot contain spaces. If your primary category has a space (e.g., “United States”), you must replace the space with an underscore when naming the range (United_States).

Step 2: Create the Primary Drop-Down List

Now, create the first drop-down menu where the user selects the country.

1. Select the cell where you want the primary drop-down (e.g., D2).
2. Go to the Data tab and click Data Validation.
3. Under the “Allow” drop-down, select List.
4. In the “Source” box, type: =Countries
5. Click OK. You can now select “USA” or “Canada” in cell D2.

Step 3: Create the Dependent Drop-Down List using INDIRECT

Finally, create the secondary list that reacts to the first cell’s selection.

1. Select the cell next to your primary drop-down (e.g., E2).
2. Go to the Data tab and click Data Validation.
3. Under the “Allow” drop-down, select List.
4. In the “Source” box, type the INDIRECT formula referencing the primary cell:

   =INDIRECT(D2)

5. Click OK. (If D2 is currently empty, Excel may warn you that the source evaluates to an error. Click Yes to continue.)

Now, when you select “USA” in D2, clicking E2 will only show American cities. If you change D2 to “Canada”, E2 will dynamically update to show Canadian cities, drastically improving the accuracy and usability of your Excel forms.