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 Dependent Dropdown Lists.


Let us go through the steps 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.

Indirect - Dependent Dropdown List 01


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.

Indirect - Dependent Dropdown List 02


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

Indirect - Dependent Dropdown List 03


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:

Indirect - Dependent Dropdown List 04


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

Indirect - Dependent Dropdown List 05

Go to Data > Data Validation

Indirect - Dependent Dropdown List 06


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.

Indirect - Dependent Dropdown List 07

Click OK. Try out your drop-down list:

Indirect - Dependent Dropdown List 08


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 dependent drop-down list.

Indirect - Dependent Dropdown List 09

Go to Data > Data Valdiation

Indirect - Dependent Dropdown List 06


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.

Indirect - Dependent Dropdown List 10

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

Indirect - Dependent Dropdown List 11


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:

Indirect - Dependent Dropdown List 12

Indirect - Dependent Dropdown List


How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Calculate Elapsed Time in Excel When you have two points in time and you want to calculate the amount of time elapsed, then you will need to use Excel´s TEXT function Sometimes data gets dumped into Excel with the following date & time format: 24/01/2015  19:48:00. Using the TEXT function and enter...
Excel´s EndOfMonth function The EOMONTH (EndOfMonth) function in Excel is one that most people do not use because they just don't know that it exists. It is a great Excel function to use if you want to see when the month end date is from a current date's value. So if you have sales reps who make a sal...
Create a Dynamic Data Range with the OFFSET functi... What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number o...
Match Two Lists With The MATCH Function I am sure that you have come across many occasions where you have two lists of data and want to know if a specific item in List1 exists in List2. Well I have! With the MATCH function you can verify if a cell´s item in List1 exists in List2. The function will return the r...