INDIRECT Function for Dependent Dropdown Lists in Excel

The INDIRECT function is really cool as it opens up a lot of interesting combinations in Excel.

We will use the power of the INDIRECT function right now on creating a Conditional Drop Down list Excel.

Watch the data validation indirect in Excel on YouTube and give it a thumbs-up!

Let us go through the steps on the dependent drop down list Excel in detail:

 

STEP 1: We have our data ordered in the following columns: Category, Meat, Beverage, Breakfast. 

Notice that the Category column has the values Meat, Beverage, and Breakfast. You will see why in our example below.

 

STEP 2: We are going to assign Named Ranges for all four columns.

The Name Box is on the far left of the Formula Bar.

 

Highlight the Category values, and type in the Name Box the name Category

 

STEP 3: Do the same for the other three columns:

Meat column values – Meat (Named Range)

Beverage column values – Beverage (Named Range)

Breakfast column values – Breakfast (Named Range)

After you created all these Named Ranges, click on the Name Box dropdown and see our newly created Named Ranges:

 

STEP 4: Let us start creating the dropdown lists, select the cell you want to place the first dropdown list.

Go to Data > Data Validation

 

STEP 5: Choose List in the Allow drop-down, and in the Source area, type in =Category

The reason we are doing this is to use the Category Named Range we defined in Step 2.

Click OK. Try out your drop-down list:

 

STEP 6: The moment you have been waiting for, it’s time to use our INDIRECT function!

Select the cell where you want to place the indirect data validation list.

Go to Data > Data Valdiation

 

STEP 7: Choose List in the Allow drop-down, and in the Source area, type in =INDIRECT($H$10) 

This will return the Named Range values from the drop-down list selected in cell H10.

Click OK. You will get this error initially, just click Yes to continue:

 

For example, if we pick Meat in the Category List dropdown, INDIRECT will calculate this as the “Meat” Named Range we defined earlier and return its values in the Content List dropdown.

The Meat Named Range would represent the values: Beef, Chicken, Pork: