Dynamic Drop-Down Lists in Excel Using INDIRECT: How-To

Formulas & functions · Updated November 19, 2026

Yes. To make a dependent drop-down in Excel, create a named range for each list, then set the second list’s Data Validation source to =INDIRECT($A2), assuming the first drop-down is in A2 and its choices match the named ranges. Choosing a category in A2 then determines which items appear in the drop-down in B2.

How INDIRECT Creates a Dependent List

Excel’s Data Validation feature can show a list of values in a cell. A dependent drop-down uses the value selected in one cell to choose the source for another cell’s list. In this setup, named ranges hold the item lists, and INDIRECT turns the text in the category cell into a reference to the matching named range.

For example, a category cell might contain “Fruit,” and a workbook-level named range called Fruit might refer to a range containing Apple, Pear, and Orange. The formula =INDIRECT($A2) tells Excel to use the range whose name matches the value in A2. Selecting “Vegetables” instead points the second drop-down to the Vegetables range.

INDIRECT does not search a table for matching records or build the list by itself. It interprets text as a reference. The named ranges are what connect the category names to the corresponding list items, so their names and the category values must agree.

Set Up the Lists and Named Ranges

First, put the source data on a separate worksheet, perhaps one named Lists. For this example, place category names in A2:A4 and put each category’s items in its own column, with the category name at the top:

  • Lists!A2:A4: Fruit, Vegetables, Drinks.
  • Lists!B1: Fruit; B2:B4: Apple, Pear, Orange.
  • Lists!C1: Vegetables; C2:C4: Carrot, Pepper, Spinach.
  • Lists!D1: Drinks; D2:D4: Water, Tea, Juice.

Create a named range for each set of items, excluding the heading. Select B2:B4, click the Name Box to the left of the formula bar, type Fruit, and press Enter. Repeat for C2:C4 with the name Vegetables and D2:D4 with the name Drinks. Use names that begin with a letter or underscore and do not contain spaces; Excel names cannot be written like ordinary labels with spaces.

For the category choices, select Lists!A2:A4 and create a workbook-level named range called Categories in the same way. Check the names by opening Formulas > Name Manager. The Refers To entry for Fruit, for instance, should point to the three fruit cells. A named range is a reference, not a copy of the values, so edits to those cells will be reflected in the list.

Create the First and Dependent Drop-Downs

On an entry sheet, use A2 for the category and B2 for the dependent choice. Select A2 and open Data > Data Validation. In the Settings tab, choose List from the Allow menu. In the Source box, enter =Categories, then select OK. This gives A2 the category choices from the named range.

With B2 selected, open Data > Data Validation again, choose List, and enter =INDIRECT($A2) in Source. Select OK. The dollar sign fixes the column at A while leaving the row relative. If you later copy the validation down column B, the source for B3 will refer to A3, and the source for B4 will refer to A4.

Test the result by selecting Fruit in A2 and opening the drop-down in B2. It should offer Apple, Pear, and Orange. Change A2 to Drinks and B2 should offer Water, Tea, and Juice. In the Data Validation dialog, you can leave In-cell dropdown selected so users can open the list from the cell. The Error Alert tab can also display a message when someone enters a value that is not allowed.

Handle Category Names That Contain Spaces

Named ranges cannot contain spaces, but category labels often do. One practical convention is to replace spaces in the range names with underscores. For example, use the category label “Hot Drinks” in the first list and name its item range Hot_Drinks. In the second drop-down’s Source box, use =INDIRECT(SUBSTITUTE($A2," ","_")). SUBSTITUTE changes the space in the selected label to an underscore before INDIRECT interprets it as a name.

This approach works only if the transformed text matches the name exactly. If categories contain punctuation, leading or trailing spaces, or other characters that are not valid in a defined name, the simple substitution may not be enough. In those cases, use a consistent naming scheme and either make the displayed labels match valid names or create a separate mapping between labels and range names.

Make the Item Ranges Expand When Data Changes

The basic example uses fixed ranges, so adding a fourth fruit below Orange will not automatically include it in the Fruit name if that name still refers only to B2:B4. You can edit the range in Formulas > Name Manager when the list changes, or define a range that adjusts to the number of filled cells.

For example, with a heading in Lists!B1 and a contiguous list beginning in B2, set the Fruit name’s Refers To field in Name Manager to =Lists!$B$2:INDEX(Lists!$B:$B,MAX(2,COUNTA(Lists!$B:$B))). This reference starts at B2 and ends at the last counted row in column B, counting the heading as well as the items. With the heading in B1 and three items in B2:B4, the range is B2:B4. If a fourth item is entered in B5, the range expands to include B5.

This defined-name formula assumes the list has a heading, contains at least one item, and has no blank cells between items. A blank in the middle can make COUNTA count fewer cells than the actual last used row, so the resulting range may omit a value at the bottom. Use the equivalent setup for each category column, changing the column reference. If you add categories, create matching named ranges and add the new category to the Categories source list.

Apply the Validation to More Rows

To make the form usable for multiple records, copy B2 and paste it into the cells below, or select the intended range in column B before setting up its validation. The source reference should use a relative row, as in =INDIRECT($A2), rather than locking the entire reference as =$A$2. With the relative row, each row’s dependent list responds to the category in that same row.

Users should choose the category before choosing the dependent item. If they change a category after selecting an item, Excel does not necessarily clear the old item automatically. For example, changing Fruit to Drinks can leave “Apple” in the second cell even though Apple is not a drink. The validation restricts future entries; it does not guarantee that an existing value remains valid after its parent choice changes. A user should reselect the dependent value, or a workbook designer can add a separate check or automation if automatic clearing is required.

Common INDIRECT Problems and Fixes

  • The dependent list is empty or shows an error: Confirm the selected category corresponds to a named range, and that the name refers to cells containing the intended items. Check spelling in Formulas > Name Manager.
  • The category text has spaces: Use a valid range name such as Hot_Drinks and the SUBSTITUTE version of the validation formula, or revise the labels to use a consistent naming convention.
  • The list works in one row but not another: Check that the formula uses a relative row reference such as $A2. When applied lower down, it should become $A3, $A4, and so on.
  • New items do not appear: The named range may be fixed to the original cells. Expand it in Name Manager or use an expanding reference like the INDEX example above.
  • The drop-down accepts unexpected typed text: In Data Validation, check the Error Alert tab and make sure the alert is enabled and set to Stop if users must not enter other values. Validation is not a security control; pasted data or other workbook operations can still create invalid entries.

Also check the scope of each name in Name Manager. Workbook-level names are generally easiest for this pattern because the validation cell can refer to them from another worksheet. If a name is scoped only to a particular sheet, it may not resolve as expected elsewhere. INDIRECT is also volatile: Excel recalculates it whenever the workbook recalculates. A few dependent lists are usually straightforward, but a very large workbook with many volatile formulas may benefit from a design that uses fewer INDIRECT formulas.

When to Use INDIRECT, and What to Use Instead

INDIRECT is useful when each category has its own well-defined list and you want a familiar, easy-to-maintain dependent drop-down. It is particularly convenient for forms with a modest number of categories, because each list has a visible name and a clear source range. It also works in versions of Excel that do not include newer dynamic-array functions.

The trade-off is that every category needs a corresponding name, and adding or renaming categories requires keeping those names in sync. INDIRECT also relies on text matching and recalculates frequently. If the source data is a single table with Category and Item columns, a formula-driven helper list using functions such as FILTER can be a more flexible alternative in Microsoft 365 or Excel 2021 and later. That design can avoid maintaining a separate named range for every category, but the helper formula and Data Validation source need to be set up to suit the workbook.

For a short list that rarely changes, fixed named ranges are simplest. For lists that grow regularly, use expanding names or consider a table-based design. Choose based on how the source data is organized, how many categories you expect, and whether the workbook must support older Excel versions.

Frequently Asked Questions

Does INDIRECT work in Excel for Microsoft 365?
Yes. INDIRECT is available in Excel for Microsoft 365, and the named-range approach works in desktop Excel for Windows. In Excel for the web, available editing options can differ, so check the Data Validation controls in the web interface if you need to create or change the rules there.

Why does Excel say the source evaluates to an error?
The text produced from the category cell probably does not match an existing name, or that name refers to an invalid range. Compare the category value with the name in Formulas > Name Manager, including spaces, underscores, and spelling.

Can I use category names with spaces?
Not directly as defined names. Replace spaces in the names with underscores and use =INDIRECT(SUBSTITUTE($A2," ","_")), or choose category labels that match valid range names.

Will changing the first drop-down clear the second cell?
No. Data Validation controls the choices available when a user enters a value; it does not automatically remove a value already in the cell. After changing the category, reselect the dependent choice and consider adding a validity check if stale choices matter.

Can I use INDIRECT with an Excel table?
Yes, if the text supplied to INDIRECT matches a defined name that refers to the relevant table column or range. However, INDIRECT does not automatically infer which table rows match a category. For a single table of categories and items, a filtered helper list may be easier to maintain.

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.