How to Create Dependent Drop-Down Lists for Multiple Rows in Excel
Question details
The user wants to set up dependent drop-down lists that work dynamically across multiple rows without having to manually adjust the formula for each row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying data validation to a large dataset where a secondary drop-down list depends on the selection made in the same row's primary drop-down list.
- Observed behavior
- The user needs the correct relative reference in the data validation formula so each row dynamically reads its own adjacent cell instead of being locked to the first row.
Ensure that your source data is organized into Named Ranges that exactly match the options available in your primary drop-down list, and avoid using spaces or special characters in those names.
Use the INDIRECT Function with Relative Row References
The most reliable way to create dependent drop-down lists across multiple rows is by using the INDIRECT function combined with a mixed cell reference.
When setting up Data Validation for multiple rows at once, the key is to lock the column but leave the row relative (e.g., $A2). This ensures that when the rule is applied down the column, row 3 looks at A3, row 4 looks at A4, and so on.
Highlight the entire column or range of cells where you want the dependent drop-down lists to appear (for example, B2:B100).
Navigate to the Data tab on the ribbon menu and click on the Data Validation button.
In the Settings tab, change the 'Allow' drop-down to 'List'. In the 'Source' text box, begin typing your formula.
Type =INDIRECT($A2), assuming column A contains your primary drop-down selections. Ensure there is no dollar sign before the row number. Click OK to apply the rule.
Use XLOOKUP for Dynamic Dependent Drop-Downs
XLOOKUP can be used inside data validation to fetch dynamic arrays without needing to define named ranges for every category.
Create Dynamic Drop-Downs Easily in WPS Spreadsheet
WPS Spreadsheet offers robust Data Validation tools that fully support the INDIRECT function and Named Ranges, allowing you to build dynamic, multi-row dependent drop-down lists effortlessly without lag.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file where you want to add your dependent drop-down lists.
- 2. Access Data Validation: Highlight your target cells, go to the 'Data' tab on the top ribbon, and select 'Validation'.
- 3. Apply the INDIRECT formula: Choose 'List' under the criteria settings, enter =INDIRECT($A2) ensuring the row number is unlocked, and click OK to finish.
Frequently Asked Questions
Why is my INDIRECT formula evaluating to an error in Data Validation?
This usually happens if the primary cell (e.g., A2) is empty when you create the data validation rule, or if the text in the primary cell doesn't exactly match your defined Named Ranges. It will work normally once a valid option is selected in the primary cell.
How do I remove the dollar sign in the data validation formula?
When you click on a cell to select it in the Data Validation Source box, the software automatically makes it an absolute reference (e.g., $A$2). Simply click into the text box, place your cursor after the dollar sign preceding the row number, and press Backspace to delete it (resulting in $A2).
Can I use spaces in my named ranges for the drop-down lists?
No, spreadsheet software does not allow spaces in named ranges. If your primary drop-down items have spaces, use the SUBSTITUTE function in your validation formula, such as =INDIRECT(SUBSTITUTE($A2," ","_")), and name your ranges with underscores instead of spaces.
