How to Create a No-Macro Decision Tree with Dependent Drop-Down Lists in WPS Spreadsheets
Question details
The user wants to create a macro-free Excel decision tree in WPS Spreadsheets with dependent data-validation drop-down lists, where later choices filter according to earlier selections and invalid combinations are prevented or identified.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a product configurator or decision tree with cascading drop-down choices without using VBA or an XLSM workbook.
- Observed behavior
- When an earlier selection changes, dependent selections can remain stale or become invalid; the user needs a formula/data-validation based approach.
Prepare a source-list sheet with primary categories in one column and dependent options in adjacent columns, and give each dependent column a header that exactly matches a primary category name.
Create Dependent Drop-Downs with Named Ranges and INDIRECT in WPS Spreadsheets
Use this as the main no-macro method when you want the second-level drop-down to filter based on the first selection.
This method uses WPS Spreadsheets' Data Validation and named ranges. The dependent list source is built with the INDIRECT function, so the second drop-down reads the range name returned by the first selection. It remains a standard .xlsx workbook with no VBA.
Open WPS Spreadsheets and create a new sheet. Put the primary categories in column A, then enter each category's dependent options in the adjacent columns using the category names as the column headers.
Select the dependent option cells for one category, go to Formulas > Name Manager, click New, and enter the exact category name as the range name. Repeat for each dependent list.
Click the first decision cell, go to Data > Data Validation, set Allow to List, and enter the primary category range such as =Source!$A$2:$A$10. Click OK.
Click the second decision cell, go to Data > Data Validation, set Allow to List, and enter =INDIRECT(A2) in Source, replacing A2 with the cell containing the primary selection. Click OK.
Change the primary drop-down and then open the dependent drop-down to confirm the filtered list. If the old dependent value no longer matches, clear it manually or add a helper column that flags invalid combinations.
Identify the Matching Product with a Formula-Based Decision Table
Use this optional lookup approach when the decision tree must return a specific product or result instead of only filtering lists.
Create a Macro-Free Decision Tree in WPS Spreadsheets
WPS Spreadsheets can build a dependent drop-down decision tree using named ranges, Data Validation, and the INDIRECT function. You get the same interactive behavior as Excel without macros or XLSM files, while saving as a standard .xlsx workbook.
- 1. Prepare the workbook: Open WPS Spreadsheets and create a source sheet for primary categories and their dependent options.
- 2. Define named ranges: Select each dependent option list, click Formulas > Name Manager > New, and name it exactly after the corresponding primary category.
- 3. Create the primary drop-down: Select the first decision cell, click Data > Data Validation > List, and enter the primary category source range.
- 4. Link the dependent drop-down: Select the next cell, click Data > Data Validation > List, and enter =INDIRECT(first_cell) in Source, using the actual first selection cell.
- 5. Test and save: Change the primary selection to verify filtered dependent options, then save the workbook as .xlsx to remain macro-free.
Frequently Asked Questions
Can I create dependent drop-down lists in WPS Spreadsheets without using macros?
Yes. Use Data Validation with the List option and create named ranges for each dependent list, then set the dependent cell source to =INDIRECT(A2), where A2 is the primary drop-down cell.
Why does my dependent drop-down keep the old value when the first selection changes?
Data Validation does not automatically clear a cell when its source changes. You can clear the dependent cell manually, apply conditional formatting to highlight invalid values, or add a helper formula to flag mismatched combinations.
Does this dependent drop-down method work when the file is opened in Microsoft Excel?
Yes. The named range and INDIRECT approach creates a standard .xlsx workbook. It does not require VBA or an XLSM file, so it opens normally in Microsoft Excel.
How should I name the ranges for dependent lists?
Name each range exactly after the corresponding primary category text, and avoid spaces or special characters. For example, if the primary category is 'Laptop', name the dependent range 'Laptop' or 'Laptop_Options' and reference that exact name in the INDIRECT formula.
Can a formula return the final product after two drop-down selections?
Yes. Add a reference table with each valid combination and use a lookup formula such as INDEX/MATCH or LOOKUP with multiple conditions to return the matching product, wrapped in IFERROR to handle invalid combinations.
