Answer

To change a drop down list based on a cell value, use dependent data validation with named ranges or structured tables so the list updates automatically when the controlling cell changes.

Method 1: Using Named Ranges (Recommended for static datasets)

What is the principle behind this method?

A controlling cell determines which named range Excel uses as the source for the dependent drop down list.

How to set up the data structure

1. Place category headers in separate columns.

2. Enter list items under each header with no blank rows.

3. Ensure header names contain no spaces or replace spaces with underscores.

How to create named ranges

1. Select the first list under its header.

2. Click in the Name Box.

3. Type the header name exactly.

4. Press Enter.

5. Repeat for all lists.

How to create the primary drop-down

1. Select the controlling cell.

2. Open Data Validation.

3. Choose List.

4. Enter the category headers as the source.

How to create the dependent drop-down

1. Select the dependent cell.

2. Open Data Validation.

3. Choose List.

4. Enter the following formula as the source: 

   =INDIRECT(A1)

A1 is the controlling cell.

Why INDIRECT works

INDIRECT converts text from the controlling cell into a reference that matches a named range.

Method 2: Using Excel Tables (Recommended for scalable datasets)

What is the principle behind this method?

Excel tables expand automatically, allowing the drop down list to grow without editing formulas.

How to set up the table

1. Convert the dataset into a table.

2. Ensure the first row contains headers.

3. Use one column per category.

How to create dynamic named ranges

1. Open Name Manager.

2. Create a new name.

3. Use a structured reference as the source.

How to apply data validation

1. Select the dependent cell.

2. Choose List in Data Validation.

3. Reference the named range.

Method 3: Using Dynamic Array Formulas (Excel 365 and Excel 2021)

What is the principle behind this method?

Dynamic arrays filter values based on a condition without named ranges.

How to build the filtered list

1. Create a helper range.

2. Enter this formula: 

   =FILTER(B2:D10,B1:D1=A1)

How to apply the drop down

1. Select the dependent cell.

2. Open Data Validation.

3. Set the source to the helper range spill reference.

Why FILTER improves accuracy

FILTER recalculates instantly when the controlling cell changes.

Windows and Mac differences

Windows and Mac use identical formulas and logic.
The Data Validation dialog location differs slightly in the ribbon layout.

Common errors and how to prevent them

1. Mismatched names break INDIRECT references.

2. Blank cells truncate drop-down lists.

3. Spaces in headers require underscores.

4. Volatile functions increase recalculation time.

Best practice recommendations

1. Use named ranges for small datasets.

2. Use tables for growing lists.

3. Avoid INDIRECT in large workbooks.

4. Validate inputs before creating dependencies.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report