Dependent Dropdown Lists

A dependent dropdown is a list that changes based on what was selected in another cell. The classic example in our table: when a user picks Tech in the Category column, the Product dropdown should show only Laptop and Phone — not Chair or Desk. Change the category to Office, and the product list switches accordingly.

This is called cascading validation — one selection drives the next.

The Logic Behind It

The trick is combining two things you already know:

• Named ranges — one per category, each pointing to the relevant product list;
• INDIRECT — to dynamically select which named range to use based on the category cell.

If your named ranges are called Tech and Office, and the category is selected in cell D2, then this formula in the Product validation field: =INDIRECT(D2).

Setting It Up Step by Step

Step 1 — Prepare your lists on the Lists sheet:

• E1: Laptop
• E2: Phone
• F1: Chair
• F2: Desk

Step 2 — Create a named range for each category:

• Select E1:E2 → in the Name Bar type Tech;
• Select F1:F2 → in the Name Bar type Office.

Step 3 — Apply validation to the Product column:

1. Select the Product column cells (E2:E51);
2. Open Data Validation → Settings → List;
3. In Source, type: =INDIRECT(D2) — where D2 is the first Category cell;
4. Click OK

One Known Limitation

If the Category cell is blank, INDIRECT has nothing to resolve and Excel will throw a validation error when the user clicks the Product dropdown. You can suppress this by checking Ignore blank on the Product validation rule — covered in Section 1, Chapter 5.

Task

1. Test by selecting Tech in Category — confirm only Laptop and Phone appear in Product column;
2. Change Category to Office — confirm the Product list switches to Chair and Desk or check any cell in the Product column next to Office value in the Category column (e.g. E4 cell);
3. Go to the Lists sheet and add Tablet below Phone in column E;
4. Open Formulas → Name Manager, find the Tech named range, and expand it to include the new row (E1:E3);
5. Check the Product dropdown again — confirm Tablet now appears.