WPS Spreadsheets: Create Cascading Drop-Down Lists with Multiple Criteria
Question details
The user needs to create dependent drop-down lists in which the third list changes according to selections made in the first two lists.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet uses selections such as dietary category and dish type to determine the available dishes in a third drop-down list.
- Observed behavior
- The user wants the third data-validation list to return only values matching both earlier selections.
Place the source values in a structured table with separate columns for the first criterion, second criterion, and final list value. Choose three destination cells for the selections, and confirm that the source values do not contain inconsistent spaces or spelling.
Use a helper key and FILTER to build the third drop-down list
Use this approach when the source table is likely to grow or when the third list must match two selections dynamically.
A helper key combines the first two criteria into one value, while FILTER returns only the matching final values. This keeps the source data in one maintainable table and avoids creating a separate named range for every combination.
In WPS Spreadsheets, place the first criterion in column A, the second criterion in column B, and the final options in column C. For example, use Category, Dish Type, and Dish.
In D2, enter =A2&"_"&B2, then fill the formula down the source table. This creates keys such as Vegetarian_Main.
Select the first destination cell, open the Data tab, choose Data Validation, set Allow to List, and select the unique values from the Category column. Repeat the process for the second destination cell using the Dish Type values.
In an unused helper area, enter =FILTER($C$2:$C$100,($A$2:$A$100=$H$2)*($B$2:$B$100=$I$2),"") if H2 and I2 contain the first two selections. The formula returns dishes matching both criteria.
Select the third destination cell, choose Data > Data Validation, set Allow to List, and use the spilled helper range, such as =$K$2:$K$100, as the source. Change the first two selections to verify that the third list updates.
Use named ranges and INDIRECT for fixed combinations
Use this approach when the number of criterion combinations is small and each combination has a known, stable list of values.
Build multi-criteria cascading lists in WPS Spreadsheets
WPS Spreadsheets supports data validation, helper formulas, named ranges, and lookup functions for creating dependent drop-down lists. Build the source table once, combine the first two criteria, and connect the filtered results to the third list.
- 1. Prepare the source table: Open WPS Spreadsheets and arrange the criteria and final values in separate columns.
- 2. Create the criteria lists: Use Data > Data Validation > List to add the first and second drop-down lists.
- 3. Build the matching formula: Combine the first two selections with a helper key and use FILTER to return matching values.
- 4. Apply the third validation rule: Select the third cell, open Data > Data Validation, and set the filtered helper range as the list source.
Frequently Asked Questions
Can the third WPS Spreadsheets drop-down depend on two selections?
Yes. Combine the first two selections into a key, such as =H2&"_"&I2, then use FILTER to return rows that match both criteria. Use the resulting helper range as the third Data Validation list.
Where do I create a drop-down list in WPS Spreadsheets?
Select the target cell, open the Data tab, choose Data Validation, set Allow to List, and select or enter the source range for the options.
Should I use FILTER or named ranges for cascading lists?
Use FILTER when the source table changes frequently or contains many combinations. Named ranges with INDIRECT are suitable when the combinations are fixed and easy to maintain.
Why does the third list show no options?
Check that the first two selections exactly match the source table, including spaces and spelling. Also confirm that the FILTER helper range contains results and that the third validation rule references the correct cells.
Can I use this method in an Excel workbook?
Yes. WPS Spreadsheets can open and edit Microsoft Excel .xlsx files. For best compatibility, use standard formulas, data-validation rules, and named ranges supported by both applications.
