Dependent Drop-down Lists

A dependent drop-down is a list whose choices depend on another cell. Pick Rajasthan as the state and the City list offers only Jaipur, Udaipur and Jodhpur. Pick a category such as Travel on an expense claim and the sub-category list shows only Bus, Train and Cab. This keeps data entry fast and stops impossible combinations such as "Kerala, Lucknow".

You learned single drop-down lists in Data Validation & Drop-down Lists. In this lesson you will build dependent lists three ways: a modern formula method for a form, the classic INDIRECT method for every row of a register, and an OFFSET method for long lists, plus a check that catches mismatches.

The master list

Keep the allowed pairs on a sheet named Lists, as an Excel table named Cities with one row per pair:

This "long" layout, one pair per row, is easy to maintain: to add Ajmer, add a row. It is also sorted by State, which one of the methods below needs.

Method 1: FILTER and a spill reference (one form)

This method suits a form with one State cell and one City cell, such as a customer registration sheet. It needs Microsoft 365 or Excel 2021 and later.

On the Lists sheet, make two helper lists. In D2, the states without repeats:

=SORT(UNIQUE(Cities[State]))

In E2, the cities of the state chosen on the Form sheet in B2:

=FILTER(Cities[City], Cities[State]=Form!B2, "Pick a state first")

Now set up the two drop-downs on the Form sheet:

1. Select Form!B2, choose Data > Data Validation, Allow: List, and type the Source =Lists!$D$2#.
2. Select Form!B3, choose Allow: List again, with Source =Lists!$E$2#.

The # means "the whole spill range". Choose Rajasthan in B2, and the FILTER result in E2 changes to three cities, so the drop-down in B3 offers exactly those:

Jaipur
Jodhpur
Udaipur

Data validation does not accept a FILTER formula typed straight into its Source box. The formula must live in a cell, and the Source points at its spill range. Because there is only one helper list, this method works for one dependent cell, not for a whole column of rows.

Method 2: INDIRECT with named ranges (every row)

A visit register needs a State and a City on every row, and row 5's city list must follow row 5's state. The classic method gives each state its own named range and uses INDIRECT to turn the state's name into that range.

Lay out a second block on Lists with one column per state, the state name as the heading:

Select G1:I4, choose Formulas > Create from Selection, tick only Top row and click OK. Excel creates three names, one per column. Names cannot contain spaces, so the third one becomes Uttar_Pradesh.

In the register, column A has the State drop-down (Source =Lists!$G$1:$I$1). Select the City cells B2:B200, with B2 as the active cell, and set Allow: List with this Source:

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

A2 has no $, so each row reads its own state: row 7 uses A7. SUBSTITUTE changes "Uttar Pradesh" to "Uttar_Pradesh" to match the name, and INDIRECT turns that text into the range. Excel may warn "The Source currently evaluates to an error" when A2 is still empty; click Yes to continue.

This method works in every Excel version. Its weakness is maintenance: a new state needs a new column and a new name, and a renamed state silently breaks its list.

Method 3: OFFSET on a sorted list (long lists)

With hundreds of pairs, separate named columns become hard to manage. If the two-column list is sorted by State, all cities of one state sit together, so you can describe them as a block: start at the first match and take as many rows as there are matches. For the City cell in row 2 of the register:

=OFFSET(Lists!$B$1, MATCH(A2, Lists!$A:$A, 0) - 1, 0, COUNTIF(Lists!$A:$A, A2), 1)

For Rajasthan, MATCH finds it in row 5 of column A, so the block starts 4 rows below B1, at B5. COUNTIF counts 3 Rajasthan rows, so the block is B5:B7: Jaipur, Jodhpur, Udaipur. Add Ajmer as a new Rajasthan row, re-sort the list, and the block grows to four rows with no new names. This also works in all versions.

Catching mismatches

Data validation checks a cell only when someone types in it. If a user picks Kerala and Kochi, then changes the state to Rajasthan, Kochi stays in the City cell. Add a check column in C2, filled down:

=IF(B2="", "", IF(COUNTIFS(Cities[State], A2, Cities[City], B2)=0, "Check city", "OK"))

Kerala, Kochi       OK
Rajasthan, Kochi    Check city

Combine it with a conditional formatting rule that colours "Check city" red, as in Conditional Formatting. Use Data > Data Validation > Circle Invalid Data too, which circles cells whose current value breaks their rule.

Common mistakes

• Typing FILTER in the Source box. Data validation rejects dynamic array formulas there. Put the formula in a cell and use its spill reference, such as =Lists!$E$2#.
• A $ before the row in INDIRECT. =INDIRECT($A$2) makes every row follow the first state. Keep the reference relative: A2.
• Spaces in the names. "Uttar Pradesh" cannot be a name; use SUBSTITUTE to add underscores.
• Unsorted lists with OFFSET. If a Rajasthan row sits among Kerala rows, the block includes the wrong cities.
• Assuming validation fixes old entries. Changing the parent cell does not clear the child. Use a check column.

Key points

• A dependent drop-down's list changes with the value of another cell.
• For a single form, put FILTER in a helper cell and point the Source at its spill range with #.
• For every row, use INDIRECT with named ranges created by Formulas > Create from Selection.
• For long sorted lists, OFFSET with MATCH and COUNTIF returns the matching block.
• Validation does not re-check old entries; add a COUNTIFS check column or Circle Invalid Data.

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

=IF(B2="", "", IF(COUNTIFS(Claims[Category], A2, Claims[Sub], B2)=0, "Check", "OK"))