How to Create Dependent Drop-Down Lists from Tables on Multiple Sheets in WPS Spreadsheets
Question details
The user needs to create two-level data validation lists where the second drop-down filters options based on the value selected in the first drop-down, using existing tables located on multiple sheets without always building a separate lookup table.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a form or data-entry sheet with dependent drop-down lists sourced from tables on different sheets.
- Observed behavior
- The second drop-down should display only the options related to the first selected value, using existing table data from multiple sheets.
Verify that your source tables have clear category and option columns, and confirm the workbook is saved in .xlsx or .et format so data validation and formulas work correctly in WPS Spreadsheets.
Create a Dynamic Dependent Drop-Down with FILTER and a Spilled Helper Range
Use this method when your WPS Spreadsheets version supports dynamic array formulas; it updates automatically as source tables change.
This method uses a helper column where FILTER returns the matching options for the selected category. The validation source can then point to the spilled range with the # notation.
Open the workbook in WPS Spreadsheets, select the source data range, click Insert > Table to convert each category/option list into a table, and note the table and column names such as Table2 and Option.
Click the cell that will hold the first drop-down, for example A2, go to Data > Data Validation, choose List, and set Source to the category list such as =Table2[Category] or a named range.
In an empty helper cell such as H2, enter =SORT(FILTER(Table2[Option],Table2[Category]=A2,"")) and press Enter. If your version does not support SORT, use =FILTER(Table2[Option],Table2[Category]=A2,"").
Click the dependent drop-down cell, for example B2, go to Data > Data Validation, choose List, and set Source to =H2# to reference the full spilled range.
Use Named Ranges and INDIRECT for Compatibility with Older WPS Spreadsheets
Use this method if dynamic array formulas are not available or you need a static Excel-compatible setup.
Create Dependent Drop-Down Lists in WPS Spreadsheets
Use Data Validation together with FILTER or INDIRECT to build two-level drop-downs from tables on multiple sheets in WPS Spreadsheets. Your workbook remains compatible with Excel .xlsx files, and the same formulas transfer cleanly.
- 1. Open WPS Spreadsheets and prepare source tables: Open your workbook, select each source range, click Insert > Table, and make sure each table has a Category column and an Option column.
- 2. Add the first data validation list: Click the cell for the first drop-down, go to Data > Data Validation, choose List, and set Source to the category column, such as =Table2[Category].
- 3. Create the dependent helper formula or named ranges: In an empty helper cell enter =SORT(FILTER(Table2[Option],Table2[Category]=A2,"")) for dynamic versions, or create named ranges and use =INDIRECT(A2) for older compatibility.
- 4. Link the second drop-down: Click the dependent cell, go to Data > Data Validation, choose List, and set Source to =H2# for a spilled range or =INDIRECT(A2) for named ranges.
- 5. Test the dependent list: Select different values in the first drop-down and confirm the second list updates to show only matching options.
Frequently Asked Questions
Can I create dependent drop-down lists without a separate lookup table?
Yes. Use existing WPS Spreadsheets tables with structured references. In supported versions, you can use =FILTER(Table2[Option],Table2[Category]=A2,"") in a helper cell and point the second validation Source to the spilled range such as =H2#.
What formula works in older WPS Spreadsheets versions that do not support FILTER?
Create a named range for each category and use =INDIRECT(A2) as the second data validation Source. The selected first value must exactly match the named range name.
Why does my dependent drop-down show blank or all options?
Confirm that the second validation Source points to the helper spill range with # or to the correct INDIRECT range. Also ensure the first and second source columns have no leading spaces and that category names match exactly.
Can the source tables be on different sheets?
Yes. Use structured table references or named ranges that point to the specific sheet and range. For cross-sheet data validation, a helper column on the active sheet or a named range is usually the most reliable method.
