Excel Data Validation INDIRECT Formula: Dependent Lists

In Excel for Microsoft 365 on Windows, use =INDIRECT($A2) as the Data Validation list source in B2 when A2 contains the name of a named range. This creates a dependent drop-down: the choices in B2 change according to the category selected in A2.

What the INDIRECT formula does

Data Validation can restrict a cell to a list, while INDIRECT turns text into a cell reference. Put together, the two features let one drop-down select a category and a second drop-down show only the items associated with that category. For example, choosing Fruit in A2 can make B2 offer Apple, Pear, and Peach.

The key is that the text in the category cell must correspond to a named range. If A2 contains Fruit, a workbook-level name called Fruit must refer to the cells containing the fruit choices. The formula reads the text in A2, resolves it as a reference, and gives Data Validation the corresponding list. INDIRECT does not build or match categories by itself; the named ranges and labels have to be set up consistently.

This approach is often called a dependent drop-down or cascading list. It is useful for forms, order sheets, and trackers where a later choice depends on an earlier one. It does not automatically clear a previous selection if the first choice changes, so users may need to choose the second value again.

Set up named ranges for the lists

First, put each group of choices in its own range. For a simple example, place Fruit, Drinks, and Snacks in a category list, then put each category’s choices in a separate column or block. Keep the category labels and the names of the corresponding ranges aligned.

Select the cells containing the first group’s items, excluding any heading.
Choose Formulas > Define Name. Enter a name that matches the category, such as Fruit, and confirm that the reference points to the intended cells.
Repeat for each group. Names such as Drinks and Snacks can refer to their own lists.
Create a name called Categories for the cells containing the category labels, if you want to use that name for the first drop-down too.

Excel names cannot contain spaces, and they cannot be written like ordinary cell addresses. Names also have rules about their first character and punctuation. Simple category names such as Fruit are easiest. If you already have labels that include spaces, use a consistent naming method described below rather than trying to create a name with a space.

Create the first and dependent drop-downs

For this example, assume the category labels are in a named range called Categories, the category cell is A2, and B2 is where the dependent choice will go. Select A2, then choose Data > Data Validation. In the Settings tab, choose List for Allow and enter the following in Source:

With the category values defined as the named range Categories, use this Data Validation Source for A2: =Categories. The drop-down in A2 will show the values in that named range.

Next, select B2 and open Data > Data Validation. Choose List and enter the dependent source formula. With the category selected in A2 and named ranges Fruit, Drinks, and Snacks matching the possible labels, use this Source for B2: =INDIRECT($A2).

Click OK, select a category in A2, and then open the drop-down in B2. The entries should come from the named range with the same name as the value in A2. In the Data Validation dialog, the In-cell dropdown option should be enabled if you want the arrow to appear when the cell is selected. The Error Alert tab can also be used to show a message or prevent entries that are not in the list.

Apply the formula to more than one row

For a working table, you will usually want a category and dependent choice on every row. Put the first category in A2 and the dependent value in B2, then apply each validation rule down the relevant columns. When setting the rule for B2:B100, use the row-relative reference shown below. The dollar sign fixes the column at A, while the row number adjusts for each row.

With each row’s category in column A and its dependent cell in column B, set the Source for the range B2:B100 to =INDIRECT($A2). Excel evaluates the relative row for each cell: B2 uses A2, B3 uses A3, and so on.

One reliable way to copy the rule is to create it in B2, copy B2, select B3:B100, and use Paste Special > Validation. You can also select the full target range before opening Data Validation, but verify the reference is anchored to the first row of that selected range. Avoid changing the formula to =INDIRECT($A$2) for a column of independent records: the absolute row would make every dependent list follow A2.

Handle spaces in category labels

Suppose a category is labelled Hot Drinks, but Excel cannot create a named range containing a space. One straightforward solution is to name the corresponding range Hot_Drinks, replacing the space with an underscore. Then make the formula perform that same replacement before INDIRECT interprets the text.

With Hot Drinks in A2, a named range Hot_Drinks, and the dependent list in B2, use this Source: =INDIRECT(SUBSTITUTE($A2," ","_")). SUBSTITUTE changes spaces in the cell value to underscores, so the resulting name matches the range name.

This example handles spaces only. If labels contain hyphens, slashes, punctuation, or other characters that are not permitted in names, decide on a consistent alternative label or mapping rather than assuming this formula will clean every character. Another option is to use short, valid category keys in the source data and show more descriptive labels elsewhere. The text-to-name match must still be exact after any substitutions.

Common problems and practical checks

The dependent drop-down is empty or errors: Check that the selected category exactly matches a defined name, including spelling and spaces. Open Formulas > Name Manager to inspect the name and its Refers to range.
Some rows show the same choices: Check that the row part of the reference is relative, as in $A2, and that the formula was applied starting on the intended row.
A category change leaves an old value behind: Data Validation controls future entries; it does not erase a value already in the cell. After changing A2, check B2 and select a valid replacement if necessary.
The list includes blanks: The named range may include empty cells. Adjust the range so it contains only the intended choices, or use a carefully maintained source range.
Users can still paste an invalid value: Validation is a useful entry control, not a complete security boundary. Review the Error Alert settings and validate important data separately if the workbook’s process requires it.

INDIRECT is a volatile function, meaning Excel recalculates it when recalculation occurs, even when its input has not changed. In ordinary workbooks with a modest number of dependent lists, this is often manageable. In a very large workbook with many such formulas, recalculation can become a consideration. Keep the workbook’s names and source ranges organized, and test its responsiveness with realistic data.

When to use INDIRECT and what to use instead

INDIRECT is a convenient choice when each category already has its own named range and the category text can be made to match that name. It is easy to inspect and works well for a small, stable set of categories. Its main maintenance cost is that adding a category means creating and checking another named range.

For a short list that never changes, a direct list entered in Data Validation may be simpler. For lists maintained in cells, a named range is easier to update than a comma-separated list typed into the dialog. For a growing catalogue or complex relationships, a helper area or a more structured design may be easier to maintain than a large collection of names. Dynamic-array formulas can help prepare lists in Microsoft 365, but Data Validation’s Source field and the way a spill range is exposed to it need to be designed carefully; do not assume that typing any worksheet formula directly into Source will work in every layout.

These steps describe Excel for Microsoft 365 on Windows, where the desktop ribbon path is Data > Data Validation. Excel for Mac has a Data Validation command on the Data tab, though dialog details and ribbon layout can differ. Excel for the web has a different interface and feature availability can vary; if you cannot create or edit the required rule there, open the workbook in the desktop app.

Frequently Asked Questions

What is the exact INDIRECT formula for a dependent drop-down?
With the category in A2 and a named range matching its text, enter =INDIRECT($A2) in the Data Validation Source for the dependent cell.

Does INDIRECT work without named ranges?
It can turn text into other valid references, but for this common Data Validation setup the category text must resolve to a reference. Named ranges are the usual way to provide the category-specific lists.

Why does my dependent list not update when I change the first selection?
Check that the category text matches the range name and that the validation formula points to the correct row. Also note that an old value already in the dependent cell is not automatically removed when the category changes.

Can category names contain spaces?
Not as Excel defined names. Replace spaces consistently in the range names and use SUBSTITUTE in the validation formula, or use another valid naming scheme. The simple SUBSTITUTE example only replaces spaces.

Can I use this formula for several rows?
Yes. Apply the validation rule down the dependent cells and use a mixed reference such as =INDIRECT($A2), so the column stays A while the row changes for each record.

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.