Easy Steps Excel Dependent Drop Down List Data Validation (2024)

Use Dynamic Lists

Because the INDIRECT function only works with references, not formulas, the previous method for dependent data validation will not work with lists that use formula-based dynamic named ranges, such as OFFSET ranges. Use one of the following solutions when a dynamic list is required:

• Named Excel Tables (best option)
• Dynamic Name Workaround (if you cannot use tables)

Named Excel Tables

To avoid the problem, use named Excel tables, instead of formula-based dynamic ranges.

• Create a table with your list items
• Select all the items in the table column (not the heading)
• Click in the Name Box, type a one-word name the range, and press Enter

Then, use one of the INDIRECT function examples shown above, to create a dependent drop-down list.

Dynamic Name Workaround

If you cannot use the Named Excel Tables, use the following method for creating dependent lists from formula-based dynamic named ranges:

1. Create the first named range and drop-down list as described above.
2. Create the supporting named lists, and name the first cell in each range, e.g., cell B1 is named Fruit and cell C1 is named Vegetables.

   

3. Name the column in which each list is located, e.g., column B is named FruitCol and column C is named VegetablesCol
4. For the second drop down, choose to Allow: List, and use a formula that calculates the lookup range. For example, if the first drop-down list is in cell E2:

=OFFSET(INDIRECT($E2),0,0, COUNTA(INDIRECT(E2&"Col")),1)

if two-word items will be used, you can include the SUBSTITUTE function in the formula:

=OFFSET(INDIRECT(SUBSTITUTE($F2," ","")),0,0, COUNTA(INDIRECT(SUBSTITUTE($F2, " ","") &"Col")),1)