Excel Dependent Dropdown List Using INDIRECT in 365

To make an Excel dependent dropdown list using INDIRECT, create a named range for each set of choices, then use =INDIRECT(SUBSTITUTE($A2," ","_")) as the Data Validation source for the second dropdown. The formula reads the selection in A2, converts spaces to underscores, and uses the matching named range as the list.

What a dependent dropdown does

A dependent dropdown changes its available choices according to a selection in another cell. For example, a first list might offer “Hot Drinks” and “Cold Drinks.” The second list then offers only the drinks belonging to the chosen category. This is useful in order forms, inventory sheets, surveys, and data-entry templates where a broad choice determines a more specific one.

Excel’s Data Validation feature creates each dropdown. INDIRECT connects the second dropdown to a range whose name is built from the first cell’s value. It does not filter a single list by itself: the source choices must already be arranged in ranges, and those ranges must have names that match the selections.

Prepare the lists and named ranges

For a small example, put the category names in Lists!A2:A3: Hot Drinks and Cold Drinks. Put the corresponding choices in separate columns, with a heading above each list:

Lists!B1: Hot_Drinks; Lists!B2:B4: Tea, Coffee, Hot chocolate.
Lists!C1: Cold_Drinks; Lists!C2:C4: Water, Iced tea, Lemonade.

The headings are a naming convention, not required data. The important part is that the named ranges use the same names: Hot_Drinks should refer to the three hot-drink choices, and Cold_Drinks should refer to the three cold-drink choices. Create another named range called Categories for Lists!A2:A3.

To create a name in Excel for Microsoft 365 on Windows, select the cells in a list, choose Formulas > Define Name, enter the name, and confirm that the workbook is the scope if you want the name available throughout the file. Repeat for each list and for Categories. You can also select a range, type its name in the Name Box to the left of the formula bar, and press Enter. Check names in Formulas > Name Manager.

Excel names cannot contain spaces, and they must follow Excel’s naming rules. Avoid names that look like cell references. Using underscores in the range names makes it possible to keep readable labels such as “Hot Drinks” in the category list while matching them to valid names such as Hot_Drinks.

Create the first dropdown

On the entry sheet, use column A for the category and column B for the dependent choice. Select A2, or select A2:A100 if you want the rule on multiple rows. Choose Data > Data Validation. In the dialog, set Allow to List, and enable In-cell dropdown if it is not already selected.

With the category list in Lists!A2:A3 named Categories, enter =Categories in the Source box. Click OK. Each selected cell in column A can now offer Hot Drinks or Cold Drinks. If you applied the rule to a range, each row gets its own dropdown.

Connect the second dropdown with INDIRECT

Select B2, or select B2:B100 to apply the dependent dropdown to several rows. Open Data > Data Validation, set Allow to List, and enter the source below. This assumes the parent selection is in A2 and the named ranges are Hot_Drinks and Cold_Drinks; when applied down the column, the row reference adjusts automatically.

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

Click OK, then test the result: choose Hot Drinks in A2 and open B2. The available choices should be Tea, Coffee, and Hot chocolate. Change A2 to Cold Drinks and B2 should offer Water, Iced tea, and Lemonade. The dollar sign fixes the parent column at A, while the row number remains relative, so B3 reads the selection in A3 when the rule is applied to that row.

SUBSTITUTE replaces every ordinary space in the selected category with an underscore. INDIRECT then interprets the resulting text as a range name. If your category labels already match the named ranges exactly, you can use a simpler source instead. For a layout with the parent selection in A2 and a named range called HotDrinks, for example, the source can be =INDIRECT($A2). The names and labels must agree exactly for that version to work.

Handle spaces and other naming differences

Using SUBSTITUTE is convenient when spaces are the only difference between a label and its range name. It does not automatically remove punctuation, change hyphens, or translate special characters. If a label is “Non-Alcoholic,” for instance, replacing spaces alone will not produce a valid range name if the chosen name uses a different spelling. Either make labels and names consistent, or build a formula that deliberately maps each label to its corresponding valid name.

For simple workbooks, keeping names predictable is usually easier to maintain than adding a long formula for many different character substitutions. Before distributing a template, check that every category has a matching named range and that each name refers to the intended cells. Formulas > Name Manager is the place to review or edit those definitions.

Extend the lists and apply validation to more rows

Validation rules do not automatically create a new named range when you add a category. To add “Desserts,” for example, add the category to the Categories source range, put its choices in a worksheet range, create a name such as Desserts for those choices, and ensure the label and name correspond. If the category list uses a label with spaces, name the range with underscores and keep the SUBSTITUTE formula in the dependent validation rule.

Similarly, a named range that points to a fixed number of cells will not necessarily include choices typed below that range. You can edit the name in Name Manager to include the new cells, or leave extra blank cells in the range when designing a small template. Extra blanks may appear as empty entries in the dropdown, so expanding the definition deliberately is often preferable.

To extend the dropdown rules, copy a validated cell and use Paste Special > Validation where available, or apply Data Validation directly to the intended cells. When setting the dependent rule across rows, start with a formula that refers to the first row of the selected area. For example, if the selected area begins at B2, use $A2—not $A$2—so each row refers to its own category.

Common problems and what they mean

The second list is empty or shows an error. Check that the category text resolves to an existing named range. Confirm spelling, underscores, and the range definition in Name Manager. A misspelled or missing name gives INDIRECT nothing useful to refer to.
Every row shows the same choices. The row reference may be locked by mistake. In a rule starting at B2, use $A2 so the row changes as the rule is applied downward.
The list works in one cell but not another. The second cell may not have the validation rule, or the rule may point to the wrong parent row. Select the cell and inspect Data > Data Validation.
A category was changed but the old subcategory remains. Data Validation does not automatically clear a value that was already in the dependent cell. If A2 changes from Hot Drinks to Cold Drinks, B2 can still contain “Coffee.” Clear or replace B2, and consider adding a visual check or other process if stale entries would cause problems.
Typing an invalid value still seems possible. In the validation dialog, check the Error Alert settings and use a Stop-style alert if users should be prevented from entering values not on the list. Pasting data can bypass or replace validation behavior, so validation is not a complete safeguard against every incorrect entry.

Benefits and limitations of INDIRECT

The INDIRECT method is easy to understand once the range names are set up, works well for a modest number of categories, and avoids writing a separate validation formula for every category. Its main trade-off is that the range names and labels have to stay coordinated. Adding or renaming a category requires maintaining both the source list and its corresponding named range.

INDIRECT is also a volatile function: Excel recalculates it when recalculation occurs, even when its text argument has not changed. In a small data-entry workbook this is rarely a practical concern, but a workbook with many INDIRECT formulas may recalculate more often than necessary. It also relies on text resolving to a reference in the workbook, so it is not a good way to depend on a closed external workbook.

For a compact workbook with stable categories, named ranges plus INDIRECT are often a clear choice. For a larger or frequently changing catalog, consider a more structured design, such as helper lists generated from a source table and formulas supported by your Excel version. Microsoft 365 includes functions such as FILTER, but Data Validation’s Source box does not simply accept every dynamic-array formula as a direct list source. A helper range or a defined name may be needed, so test the chosen design in the version and interface where the workbook will be used.

Excel for Mac and Excel for the web

The core named-range and INDIRECT approach also applies in Excel for Mac. The ribbon arrangement and dialog appearance can differ, but use the Data tab’s Data Validation command and the Formulas tab’s name tools. On Windows, the paths above use the Microsoft 365 ribbon.

Excel for the web can work with many existing dropdowns, but its available controls may differ from the desktop dialogs. If you cannot create or edit a named range or validation rule as needed in the browser, open the workbook in desktop Excel to set up or repair the rule, then save it. If other people will use the file in different Excel versions, test both dropdowns in the environment they will use.

Frequently Asked Questions

Can I use INDIRECT for a dependent dropdown with spaces in category names?
Yes. If your named ranges replace spaces with underscores, use SUBSTITUTE in the validation source, as in =INDIRECT(SUBSTITUTE($A2," ","_")). This handles spaces, not every possible punctuation difference.

Why does my dependent dropdown show an error?
Usually, the selected category does not resolve to a valid named range. Compare the category spelling with the name in Formulas > Name Manager, and confirm that the name refers to the correct cells.

Will changing the first dropdown clear the second one?
No. Excel’s validation rule controls choices made in the dependent cell; it does not automatically erase an existing value when its parent changes. Clear the old value or use a separate review mechanism.

Can I use this method for several rows?
Yes. Apply the dependent validation rule to the desired cells in column B and use a relative row reference such as $A2 when the first selected cell is B2. Each row will use its own parent selection.

Does INDIRECT work with Excel tables?
It can be used in a validation source, but table-based data and named ranges still need to be arranged so the text produced by INDIRECT resolves to the intended list. For changing lists, plan how the named ranges will expand and test the validation rule after adding entries.

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.