Function Problems

How to Create Dependent Drop-Down Lists in Excel Based on Another Cell

Guest Writer Sep 29, 2026 868 views

Question details

The user wants to configure a dependent drop-down list in Excel so that the selectable choices in one cell automatically update based on the selection made in an adjacent cell.

How to Create Dependent Drop-Down Lists in Excel Based on Another Cell

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an interactive data entry sheet or form where secondary categories must filter dynamically based on a primary category selection.
Observed behavior: When copying data validation formulas to additional rows, the dependent drop-down lists fail to adjust their references correctly, causing incorrect or static lists.

Before you start

Ensure your source data is organized into clearly defined columns and verify that your category names do not contain spaces, as Excel named ranges do not support space characters.

Solution 1Recommended

Use Named Ranges and the INDIRECT Function

This is the most reliable formula-based method for creating dependent drop-down lists. It uses the INDIRECT function to dynamically call a named range based on the primary cell's value.

By defining Named Ranges for your sub-categories, you can use the INDIRECT function within Data Validation to point to the correct list.

To ensure the drop-down lists work when copied down to other rows, it is crucial to use a relative cell reference (e.g., A2 instead of $A$2) for the primary cell.

Define Named Ranges for source data

Select the cells containing your sub-category items. Go to the 'Formulas' tab, click 'Define Name', and name the range exactly as it appears in your primary drop-down list (e.g., 'Fruits'). Repeat for all categories.

Create the primary drop-down list

Select the cell for the main category (e.g., A2). Go to 'Data' > 'Data Validation', choose 'List' from the Allow menu, and select your primary categories as the source.

Apply the INDIRECT function for the dependent list

Select the dependent cell (e.g., B2). Go to 'Data' > 'Data Validation' > 'List'. In the Source box, enter the formula =INDIRECT(A2). Ensure you remove any absolute reference dollar signs ($) from A2.

Copy the validation to other rows

Click and drag the fill handle of cells A2 and B2 down to apply the validation to subsequent rows. Because you used a relative reference, row 3 will automatically look at A3, row 4 at A4, and so on.

Use Named Ranges and the INDIRECT Function

Handle spaces in categories: If your primary categories contain spaces (e.g., 'Fresh Fruits'), use the SUBSTITUTE function in your validation source: =INDIRECT(SUBSTITUTE(A2," ","_")) and name your range 'Fresh_Fruits'.

Solution 2

Use a VBA Macro for Dynamic Data Validation

If you have complex dependencies or want to avoid setting up multiple named ranges, you can use a Worksheet_Change VBA macro to apply data validation dynamically.

Advanced Data Validation in WPS Spreadsheet

Create Dependent Drop-Down Lists Easily in WPS Office

WPS Spreadsheet fully supports advanced Data Validation, Named Ranges, and the INDIRECT function, allowing you to build dynamic dependent drop-down lists just as you would in Microsoft Excel.

1. Organize your lists: Open your workbook in WPS Spreadsheet and type out your main categories and their corresponding sub-categories into separate columns.
2. Define category names: Highlight your sub-categories, navigate to the 'Formulas' tab, and click 'Name Manager' to create Named Ranges matching your primary items.
3. Set up Data Validation: Select the target cell, go to 'Data' > 'Validation', and select 'List'. Use the primary items for the first cell, and type =INDIRECT(A2) for the secondary cell.
4. Apply across multiple rows: Drag the bottom-right corner of the configured cells down to copy the dynamic dependent drop-down lists to the rest of your data entry table.

100% format compatibility with Microsoft Excel formulas and data validationIntuitive Name Manager to quickly organize dynamic listsCompletely free, lightweight, and fast office suite alternativeSeamlessly transfers Excel macros and VBA logic if needed

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return an error saying the source evaluates to an error?

This usually happens if the primary cell you are referencing is currently empty, or if the Named Range does not perfectly match the text in the primary cell. You can click 'Yes' to continue, and it will work once you select a value in the primary cell.

How do I clear the second drop-down automatically when the first one changes?

Standard formula-based Data Validation cannot clear cell contents. To achieve this, you must use a VBA Worksheet_Change macro that detects modifications in the primary column and executes a ClearContents command on the adjacent cell.

Can I create a 3-level dependent drop-down list?

Yes, you can extend the same logic. Create a third drop-down list that uses the INDIRECT function pointing to the cell of the second drop-down list. Ensure you create Named Ranges for every possible sub-category selected in the second list.

Why aren't my dependent drop-down lists working when I copy them down?

This happens because your Data Validation formula is using an absolute reference (like =$A$2). Change it to a relative reference (like =A2) inside the Data Validation Source box before copying the cell down to other rows.

Excel & Data Problems