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.

DOWNLOAD EXCEL WORKBOOK

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

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Calculate Total Sales With An Array Formula An Array Formula performs an Excel operation (math, comparative, join or function argument) on an array or range of data.  This could be a range of cells, a worksheet reference or a defined name. One of the most popular uses of an array formula is to calculate Total Sales. ...
VLOOKUP Multiple Columns What does it do? Searches for a value in the first column of a table array and returns the sum of values in the same row from other columns (to the right) in the table array. Formula breakdown: {=SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, ))}...
Advanced SUMPRODUCT Function: Maximum Sales What makes the SUMPRODUCT function even more powerful is its ability to nest formulas, or in simple terms, add another function within the SUMPRODUCT function. Instead of getting the Total Sales for a region, we can extract the Maximum sales value simply by entering the MAX fu...
INDEX-MATCH Maximum Sales The INDEX-MATCH formula cannot only look up values to the left and right of your data but its versatility means that it can also lookup the largest values within an array. Say that you had a list of Sales Reps in one array and their corresponding Sales Values in another array....