Excel INDIRECT Drop-Down Lists: Setup and Fixes

To make a dependent drop-down list in Excel, create a named range for each set of choices, then set the second list’s Data Validation source to =INDIRECT($A2), where A2 contains the first selection and matches a range name. Create the first drop-down from a list of those category names; INDIRECT turns the selected text into a reference to the matching named range.

What INDIRECT does in a drop-down list

INDIRECT takes text that looks like a cell reference or defined name and returns a reference Excel can use. In a dependent drop-down, the text is usually the selection in a neighboring cell. For example, choosing “Fruit” in A2 can make a second drop-down show the items in the named range Fruit.

The function does not create the lists or connect them automatically. You must create the source lists, give each list a suitable name, and configure Data Validation for the cells where users will choose items. The first drop-down selects a category; the second uses INDIRECT to point to the list associated with that category.

This approach is useful for forms such as department and employee, country and city, or product type and product. It is sometimes called a cascading or dependent drop-down list. INDIRECT is a built-in Excel function, not a special Data Validation feature.

Set up the source lists and named ranges

For a simple example, put category names in A2:A3 on a sheet called Lists: Fruit and Drinks. Put the fruit choices in B2:B4 and drink choices in C2:C4. The cells in each choice list should contain the items you want users to select, such as Apple, Pear, and Orange in B2:B4. Keep category labels and list names consistent.

Select the category cells, including Fruit and Drinks, and create a workbook-level name called CategoryList. In Microsoft 365 for Windows, use Formulas > Define Name, enter the name, and confirm that Refers to points to the selected cells.
Select the fruit choices and define a name called Fruit. Select the drink choices and define a name called Drinks. Names can be created through Formulas > Define Name or checked and edited in Formulas > Name Manager.
On the entry sheet, select the cells for the first drop-down, such as A2:A20. Choose Data > Data Validation, set Allow to List, and enter the category-list name as the source.
Select the corresponding cells for the dependent choices, such as B2:B20. Open Data Validation again, set Allow to List, and use the INDIRECT source formula shown below.

For the first validation, with the category options stored in the named range CategoryList, enter =CategoryList in the Source box. For the second validation, with the first selection in column A and the dependent choice in the same row of column B, enter =INDIRECT($A2). Apply the second rule beginning at B2; the row reference is relative so it can adjust for B3, B4, and later rows.

When A2 contains Fruit, the second rule resolves to the named range Fruit and offers the entries from B2:B4. If A2 contains Drinks, it resolves to Drinks and offers that range instead. These are references to defined names, not references to the worksheet cells A2 or B2.

How to match labels that contain spaces

Excel defined names cannot contain spaces. If your visible category is “Hot Drinks,” you cannot create a name with that exact spelling. One practical convention is to use an underscore in the name, such as Hot_Drinks, and replace spaces in the selected label before INDIRECT interprets it.

Assuming A2 contains the displayed category Hot Drinks and the corresponding named range is Hot_Drinks, use this source in the dependent list’s Data Validation rule: =INDIRECT(SUBSTITUTE($A2," ","_")). The SUBSTITUTE function changes each space to an underscore, so the text resolves to the valid name. Use the same naming convention for every category that contains spaces.

Other characters can cause similar mismatches. A name cannot include spaces and must follow Excel’s defined-name rules; it also cannot be written like a cell reference, such as A1. If labels contain punctuation or other characters you do not want in names, consider using a separate mapping table or simpler internal names rather than trying to transform every possible label in one formula.

Apply the rule to multiple rows

For a form with one category and one dependent choice per row, create the first validation in A2:A20 and the second in B2:B20. In the Source box for the second rule, use =INDIRECT($A2) while the selected range begins at B2. The dollar sign fixes the category column, while the row number can change as the rule is applied down the column.

Check the rule in more than one row after setting it up. Choose Fruit in A2 and verify the choices in B2; then choose Drinks in A3 and verify B3. If you entered a formula while B2 was the active cell, Excel normally adjusts the relative row when applying the rule to the selected range. If you copy a validated cell later, inspect the copied rule if the references behave unexpectedly.

A dependent list does not necessarily erase an old choice when its parent category changes. For instance, a user could choose Apple, then change Fruit to Drinks; Apple may remain in the dependent cell even though it is no longer a valid drink. Data Validation limits new selections but is not a guarantee that existing values will be cleared or corrected. A user can reselect a valid item, or a workbook can use additional formulas or automation if automatic clearing is essential.

Keep the lists accurate as they grow

Named ranges that refer to fixed cells do not automatically include new entries typed beneath those cells. If Fruit refers to B2:B4 and you add another item in B5, edit the name in Formulas > Name Manager so its Refers to range includes B5. Likewise, update CategoryList when you add a category. Otherwise, the new item will not appear in the drop-down.

For a small, stable set of choices, adjusting the names manually is straightforward. For lists that change frequently, consider a design that uses Excel Tables or maintained dynamic named ranges, and test the resulting Data Validation source in the version of Excel used by the workbook’s audience. A table can expand as rows are added, but the validation source still needs to refer to the intended list correctly; turning a range into a table alone does not make every dependent-list setup automatic.

Also decide what should happen to blanks. If a named range includes empty cells, the drop-down may include blank entries. Keep the named ranges limited to actual choices or use a setup that deliberately excludes unused cells. This matters especially when lists are expected to grow but currently contain only a few values.

Common problems and how to fix them

The dependent list shows an error or no useful choices: Confirm that the text in the parent cell exactly matches a defined name, after any space-to-underscore conversion. Check spelling and the name’s scope in Formulas > Name Manager.
The formula appears to work in one row but not another: Make sure the Data Validation rule starts on the row referenced by its formula. For a rule beginning in B2, =INDIRECT($A2) points to A2; if the rule begins in B5, its starting-row reference should be A5.
The name exists but Excel cannot find it: A sheet-level name may only be available in its own worksheet context. For a workbook-wide dependent list, use workbook-level names or verify that the scope is appropriate. Name Manager shows each name’s scope.
A category contains spaces: Use a valid matching name such as Hot_Drinks and the SUBSTITUTE version of the formula, or rename the visible category to match a valid name directly.
New choices are missing: Expand the relevant named range and, if necessary, the category range. A fixed reference does not grow just because a value was added below it.
A previous dependent choice remains after changing category: This is expected. Data Validation does not automatically clear a cell’s old value when another cell changes.

One more distinction helps with troubleshooting: the Data Validation setting controls what can be selected from the list, but it does not repair a misspelled existing value or prevent every form of pasted data from entering a cell. If the workbook must enforce clean data, use validation together with review checks or other controls appropriate to the workflow.

INDIRECT compared with other approaches

INDIRECT is convenient when each category already has its own named range and the names can be made to correspond to the parent selections. The validation formula is short, and the same rule can be applied to multiple rows. Its main maintenance cost is keeping category labels, defined names, and source ranges aligned.

INDIRECT is a volatile function: Excel may recalculate it when the workbook recalculates, even when its direct inputs have not changed. A few dependent lists are usually simple to manage, but workbooks with many volatile formulas can be harder to maintain and may recalculate more than expected. For a large or frequently changing data model, a lookup-based or dynamic-array design may suit the workbook better, although setting up its validation source can require extra planning.

For a short, fixed list, a regular Data Validation list may be enough and avoids INDIRECT entirely. If the goal is merely to offer one list rather than a dependent list, you can use a cell range or a defined name as the source. If categories and items are stored in a normalized table, other formula-driven approaches may be preferable, particularly in current Excel versions that support dynamic-array functions. Choose based on how often the choices change, how many categories exist, and which Excel versions must open the file.

Windows, Mac, and Excel for the web

In Excel for Microsoft 365 on Windows, the main setup is through Data > Data Validation and Formulas > Define Name or Name Manager. The Data Validation workflow and INDIRECT function are also available in Excel for Mac, though the ribbon layout can differ slightly by release. On a Mac, use the same formula and defined-name logic; do not assume a Windows-only keyboard shortcut is required.

Excel for the web supports many common workbook features, but the controls available for creating or editing validation rules can differ from the desktop application and may change over time. If you cannot find a control or need to manage many defined names, open the workbook in the desktop version of Excel. When sharing a file, verify that recipients’ spreadsheet programs support the functions and validation behavior used in it.

Frequently Asked Questions

What does INDIRECT do in Excel Data Validation?
It converts text into a reference. In a dependent list, the text is commonly a selection in another cell, and the resulting reference points to the named range that supplies the choices.

What formula makes a dependent drop-down list?
If A2 contains a category that exactly matches a defined name, use =INDIRECT($A2) as the Data Validation source for the dependent cell in B2. Create the named ranges first.

Can INDIRECT work when the category name has spaces?
Not by directly using a name with spaces, because Excel defined names cannot contain spaces. Use an underscore in the defined name and replace spaces in the label with SUBSTITUTE, or adjust the labels so they match valid names.

Why does my dependent drop-down show an error?
The parent-cell text may not match a defined name, the name may have the wrong scope, or the validation formula may point to the wrong row. Check the spelling, the Name Manager entry, and the rule’s starting cell.

Does changing the first drop-down clear the second one?
No. Data Validation does not automatically remove a previously selected dependent value when the parent selection changes. The user must choose a new valid value, or the workbook needs additional logic to clear it.

Do named ranges update when I add more list items?
Not when they refer to a fixed cell range. Expand the name’s Refers to range, or use a deliberately designed expanding-list method and verify that the validation rule includes the added choices.

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.