Pinterest Pixel

INDIRECT Function for Dependent Dropdown Lists in Excel

Bryan
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.

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.

Key Takeaways

  • Create Dynamic Dependent Dropdown Lists – The INDIRECT function helps link dropdown lists so that the second list depends on the first selection.

  • Refers to Named Ranges Dynamically – INDIRECT allows you to use text values as range references, making it perfect for creating cascading dropdowns.

  • Prevents Manual List Updates – When using INDIRECT, you don’t need to manually adjust dropdown lists when new data is added.

  • Works Well with Data Validation – You can apply INDIRECT inside the Data Validation settings to create dependent dropdowns that update dynamically.

  • Useful for Advanced Lookup Scenarios – Besides dropdowns, INDIRECT can be used for dynamic formula references based on user input.

How to Use INDIRECT Function for Dependent Dropdown Lists

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.

INDIRECT Function for Dependent Dropdown Lists in Excel

 

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 Function for Dependent Dropdown Lists in Excel

 

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

INDIRECT Function for Dependent Dropdown Lists in Excel

 

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 Function for Dependent Dropdown Lists in Excel

 

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

INDIRECT Function for Dependent Dropdown Lists in Excel

Go to Data > Data Validation

INDIRECT Function for Dependent Dropdown Lists in Excel

 

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 Function for Dependent Dropdown Lists in Excel

Click OK. Try out your drop-down list:

INDIRECT Function for Dependent Dropdown Lists in Excel

 

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.

INDIRECT Function for Dependent Dropdown Lists in Excel

Go to Data > Data Valdiation

INDIRECT Function for Dependent Dropdown Lists in Excel

 

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 Function for Dependent Dropdown Lists in Excel

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

INDIRECT Function for Dependent Dropdown Lists in Excel

 

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 Function for Dependent Dropdown Lists in Excel

Frequently Asked Questions

What is the INDIRECT function used for in Excel?
The INDIRECT function converts a text string into a reference, allowing formulas to dynamically change based on input values.

How does INDIRECT help in creating dependent dropdown lists?
INDIRECT allows a second dropdown to reference a named range based on the first dropdown’s selection, enabling dependent lists.

Do I need to create named ranges for INDIRECT to work?
Yes! Named ranges must be defined for each category so INDIRECT can refer to them when creating dependent dropdown lists.

Can INDIRECT be used with tables instead of named ranges?
INDIRECT does not work directly with structured table references, but you can convert table columns into named ranges for compatibility.

What happens if the referenced named range doesn’t exist?
If INDIRECT refers to a non-existent named range, Excel will return a #REF! error, indicating an invalid reference.

If you like this Excel tip, please share it



INDIRECT Function for Dependent Dropdown Lists in Excel | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  INDEX Function with Data Validation

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...