A purchase form asks for Category, then Item. If the Item list shows all 400 products, people pick wrong ones. A dependent drop-down shows only the items of the chosen category: fewer mistakes, cleaner data.
In this article
Method 1: named ranges + INDIRECT (works in every Excel)
On a Lists sheet, put each category’s items in its own column with the category name as the header: Stationery, Electronics, Furniture. Select the block and use Formulas › Create from Selection › Top row. Excel creates one name per column.
First drop-down (A2): Data › Data Validation › List, source = the header row. Second drop-down (B2): List, with this source:
=INDIRECT($A2)Pick “Electronics” in A2 and B2 offers only electronics.
Method 2: Excel 365, straight from your data table
No helper column per category. With a product table (Products[Category], Products[Item]), put this in a spare cell, say H2:
=SORT(UNIQUE(FILTER(Products[Item], Products[Category] = $A$2)))The validation source for B2 is then =$H$2#; the # means “the whole spill”. Add a product to the table and it appears in the list automatically.
The first list can be dynamic too: =SORT(UNIQUE(Products[Category])) in G2, validation source =$G$2#.
For many rows of a form
Method 2 spills one list per formula, so it suits a single form. For a 500-row entry sheet, Method 1 (INDIRECT on each row’s own A cell) is simpler.
Catch stale choices
If someone changes A2 after picking B2, the old item stays. Flag it with a conditional formatting rule on B2:
=AND($B2<>"", COUNTIFS(Products[Category], $A2, Products[Item], $B2) = 0)Where people go wrong
| Problem | Cause | Fix |
|---|---|---|
| “Source currently evaluates to an error” | A2 is empty when you create the rule | Click Yes and carry on; it works once A2 has a value |
| List empty for one category | Name doesn’t match the text exactly (space, spelling) | Check Name Manager; use SUBSTITUTE for spaces |
| New items missing | Named range is a fixed size | Base the names on Table columns, or use Method 2 |
Practice
Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.
More: all formula combos · Excel function course.
Stuck on a step? Ask a question and the AI answers using this article.