Dependent drop-down list in Excel

Use small source lists

The workbook stores Fruit choices (Apple, Pear) in D2:D3 and Drink choices (Water, Tea) in E2:E3. Category cells A2:A5 have a Fruit/Drink validation list. Per-row helper cells F:G show the two available items for that row; item validation on B2:B5 refers to its row’s helper range.

With A2=Fruit, B2=Apple is valid. With A3=Drink, B3=Water is valid. Row 4 deliberately has Fruit with Tea, a stale value from the other category: C4 says “Choose listed item”. Row 5 has no category: C5 says “Choose category” and its helper choices are blank.

Build and test the dependency

1. Create the category list first and add its validation to A2:A5.
2. In F2:G2, use IF to return the Fruit choices when A2 is Fruit, Drink choices when A2 is Drink, or blanks otherwise. Copy down.
3. Make B2 a list based on F2:G2, then create the equivalent row-relative source for B3:B5.
4. Change A2 from Fruit to Drink. Excel may leave the old B2 value visible; check C2 before treating the record as valid.

This finite two-choice helper method avoids a volatile INDIRECT/named-range setup. For large changing catalogs, redesign the source and verify the exact Excel version and validation behavior. See basic validation first.