Excel Drop-Down List with INDIRECT: Setup and Fixes

Formulas & functions · Updated November 19, 2026

Yes. To make a dependent drop-down list in Excel, create named ranges for the choices and use =INDIRECT($A2) as the second list’s Data Validation source, where A2 contains the name of the range to show. The first drop-down then controls which options appear in the next one.

What INDIRECT Does in a Drop-Down List

Excel’s Data Validation feature can show a list of permitted entries in a cell. A basic list uses a fixed range as its source. A dependent list changes its source according to another cell: choose a department in one cell, for example, and the next cell offers only that department’s products.

INDIRECT takes text and interprets it as a cell or range reference. If A2 contains the text Sales, and the workbook has a named range called Sales, INDIRECT turns that text into a reference to the Sales range. Data Validation uses the referenced cells as the available choices. The text in the controlling cell must match the name Excel is expected to use.

This technique is often called a cascading or dependent drop-down. It is useful for forms, order sheets, surveys, and trackers where later choices depend on earlier ones. It does not automatically create the lists or names, however; those need to be set up in the workbook.

Prepare the Source Lists

For a simple example, imagine a worksheet named Lists containing department names in one column and each department’s choices in separate columns. The first drop-down will use a list of departments. The dependent drop-down will use a named range for the selected department’s choices.

  • Put the department names in a compact range, such as Lists cells A2:A4: Sales, Support, and Finance.
  • Put each department’s options in its own column, with a heading above each set. Keep the headings and range names consistent.
  • Leave out headings when you define the option ranges; otherwise, the heading itself can appear as a selectable answer.
  • Keep each set of options together and avoid blank cells inside a list if you do not want blank choices.

For example, the Sales range might contain three product names, while Support contains two service types. The exact wording does not matter as long as the name used by the formula corresponds to the intended range.

Create Workbook-Level Named Ranges

INDIRECT needs a reference it can resolve. In this setup, create a named range for each department’s option cells. Select the cells for Sales, choose Formulas > Define Name, enter Sales as the name, set Scope to Workbook, and confirm the range shown in Refers to. Repeat for Support, Finance, and any other category.

Workbook scope is a good choice when the dependent lists may be used on multiple worksheets. A worksheet-scoped name can behave differently from a workbook-scoped name and may not be visible where you expect. Review existing names in Formulas > Name Manager if a range does not resolve as intended.

Excel names cannot contain spaces. They also cannot be written like cell references, such as A1. Use simple names beginning with a letter or underscore, and use underscores instead of spaces where needed. For the straightforward setup, the controlling-cell entry must match the name exactly apart from capitalization, which Excel does not use to distinguish names.

Set Up the Two Drop-Downs

In this example, A2 will hold the department and B2 will hold the dependent choice. First create a list validation rule for A2: select the cell, choose Data > Data Validation, set Allow to List, and set Source to the department-name range. You can select the range on the Lists sheet while entering the source. Confirm that In-cell dropdown is enabled.

Next, select B2 and open Data > Data Validation. Set Allow to List and enter the formula below in Source. This formula assumes A2 contains a department name that matches a workbook-level named range, such as Sales, and that B2 is the cell whose choices should depend on A2.

=INDIRECT($A2)

When A2 contains Sales, the source resolves to the cells in the Sales named range, so B2 offers those entries. If A2 contains Support, B2 offers the entries in Support. The dollar sign fixes the reference to column A while leaving the row relative. This matters when you copy the validation rule down a column: the rule in the next row can refer to A3 rather than continuing to use A2.

Test the setup by choosing each department in A2, opening B2’s arrow, and checking that the expected choices appear. The validation rule controls entries made through the drop-down; users may still be able to paste or otherwise enter values that do not match the list, depending on how the workbook is used and its error-alert settings.

Match Labels That Contain Spaces

A common snag is a category label such as North America. Excel names cannot contain spaces, so a named range called North America is not valid. One solution is to keep the visible label in the first drop-down but create a corresponding range name with underscores. In this approach, the label North America maps to a range named North_America.

Assume A2 contains the visible category label North America, and the workbook has a named range called North_America. Use this formula as the dependent list’s Data Validation source:

=INDIRECT(SUBSTITUTE($A2," ","_"))

SUBSTITUTE changes spaces in the selected label to underscores before INDIRECT resolves the name. This handles spaces only; it does not automatically remove slashes, hyphens, or other characters that may be unsuitable in a name. When labels include varied punctuation, a separate key column can be more reliable. For instance, the displayed label can remain North America while a helper cell holds North_America for the reference.

Build More Than One Dependent Level

You can use the same principle for a three-level selection, such as Region, Country, and City. The first list offers regions; the country list depends on the selected region; and the city list depends on the country. Each level needs valid names or a mapping to names, and each validation source must refer to the appropriate controlling cell.

Plan the naming scheme before creating many ranges. For example, country names may repeat in different regions, so naming a range only after a country can create conflicts or ambiguous references. A more deliberate scheme can combine the parent and child keys, or use helper cells that translate the visible selections into unique range names. Keep the visible choices clear for users while making the underlying names unique and consistent.

Also decide what should happen when a user changes an earlier choice. Excel does not automatically erase a later selection just because its controlling cell changed. If someone chooses Sales and then selects Support, B2 may still contain a previous Sales entry even though the new list no longer offers it. Users should reselect the dependent value, or the workbook can use a separate process to clear dependent cells when a parent choice changes.

Limitations and Alternatives

INDIRECT is convenient, but it relies on text matching and named ranges. A misspelled label, missing name, or inconsistent naming convention can break the dependent list. It is also a volatile function, meaning Excel recalculates it when recalculation occurs. In a small workbook this is usually manageable; a very large workbook with many INDIRECT formulas can be harder to maintain and may recalculate more than necessary.

Keep the named ranges in the same workbook. INDIRECT is not a dependable way to build a validation list from a closed external workbook, and external references in Data Validation have limitations. If the lists need to be shared, keep the source data and dependent validation in the same file where possible.

For a small, fixed set of categories, named ranges plus INDIRECT are easy to understand. For a large or frequently changing catalogue, consider whether a structured data design, helper ranges, or a different input interface would be easier to maintain. Excel tables can make source data easier to expand, but tables do not by themselves remove the need to define how each category maps to its choices.

Microsoft 365 for Windows uses Data > Data Validation for these steps. Excel for Mac has Data Validation as well, though the layout of dialogs can differ. Excel for the web supports many common validation tasks, but some name-management and workbook-editing tasks may be easier to complete in the desktop app. If an existing rule behaves differently online, inspect it in desktop Excel and confirm the named ranges and source formula there.

Common Problems and Fixes

  • The dependent arrow is empty or shows an error: Check whether the controlling cell exactly matches an existing name, and confirm the name points to the intended cells in Formulas > Name Manager.
  • The first list works but the second does not: Make sure the dependent rule is applied to the correct cell and its Source refers to the correct parent cell. A rule copied from another row may have the wrong reference.
  • A category contains spaces: Use a matching underscore-based name and a mapping formula, or use a helper cell that stores a valid name key.
  • The drop-down includes a blank item: Check whether the named range includes empty cells. Adjust the range so it covers only the intended choices.
  • Changing the parent leaves an old child value: This is normal; standard validation does not clear the dependent cell automatically. Recheck or clear that cell after changing the parent.
  • The list stops including new options: The defined name may point to a fixed range that does not include added rows. Update the name or use a range design that expands with the source data.

For troubleshooting, select a dependent cell and revisit Data > Data Validation to inspect the Source field. Then open Name Manager and verify the exact spelling, scope, and referred-to cells of the relevant name. Testing one parent choice at a time helps distinguish a bad name from a bad validation rule.

Frequently Asked Questions

Can I use INDIRECT in Excel Data Validation?
Yes. Set Allow to List and use an INDIRECT expression as the Source. It is commonly used to make a second drop-down depend on a value selected in another cell.

Why does my INDIRECT drop-down show an error?
The text produced by the formula may not match a valid range name, or the name may point to an invalid range. Check the selected label, the formula’s cell reference, and the name in Formulas > Name Manager.

Do the category names have to be identical to the named ranges?
They must resolve to the same name. They can be different if you translate the visible label to a valid name, for example by replacing spaces with underscores or using a helper cell.

Will the dependent cell clear when I change the first drop-down?
No. Data Validation changes the choices offered; it does not automatically remove a value already in the dependent cell. Clear or reselect that value after changing the parent choice.

Can I use this method for three levels of drop-downs?
Yes. Each later list can depend on the preceding selection, but each step needs a correctly designed name or mapping. Plan for repeated labels and remember that changing an earlier choice does not clear later cells.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.