Create Dynamic Excel Dropdown Results by Division and Month in WPS Office
Question details
The user needs the green and blue result columns to update automatically from a master table whenever the selected Division or Month dropdown value changes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains a master table and dropdown cells for Division and Month; the green and blue columns should return matching records.
- Observed behavior
- The green and blue result columns should display matching master table records automatically after the Division or Month selection changes.
Confirm that the Division and Month dropdown values exactly match the master table values, including spelling and spacing. Ensure the result columns have enough empty cells below for formula spill results.
Use FILTER to Return All Matching Records for Division and Month
Use this when a Division and Month combination can match multiple rows and you want all matching records to appear in the green and blue columns.
FILTER dynamically extracts every row where both criteria are true. The formula recalculates automatically when either dropdown changes.
Click the first cell in the green result column where the dynamic results should start.
Type =FILTER(MasterTable,(MasterTable[Division]=$A$2)*(MasterTable[Month]=$B$2),"No matching records") and press Enter. Replace MasterTable, Division, Month, A2, and B2 with your actual table and dropdown cells.
Clear any data from the cells to the right and below the formula so WPS Spreadsheets can spill all matching rows into the green and blue columns.
Use XLOOKUP with Combined Criteria for a Single Matching Row
Choose this when the selected Division and Month combination should return only one record and you need a specific column value.
Build Division- and Month-Based Dynamic Dropdown Results in WPS Spreadsheets
WPS Spreadsheets can directly populate green and blue result columns from a master table using FILTER or XLOOKUP formulas that recalculate when the selected division or month changes.
- 1. Open the workbook: Open your workbook in WPS Spreadsheets.
- 2. Enter the FILTER formula: Select the first green result cell and enter the FILTER formula with Division and Month criteria.
- 3. Check the spill results: Press Enter and verify that matching rows spill into the green and blue columns.
- 4. Change a dropdown: Change the Division or Month dropdown to confirm the results update automatically.
Frequently Asked Questions
Which formula should I use if Division and Month can match multiple rows?
Use FILTER with combined criteria, such as =FILTER(MasterTable,(MasterTable[Division]=$A$2)*(MasterTable[Month]=$B$2),"No matching records"), so all matching rows spill into the result columns.
Why does my formula return #SPILL! in WPS Spreadsheets?
The spill range is blocked. Delete or move data in the cells where the results would appear, or place the formula in a location with enough empty rows and columns.
Do the results update automatically when I change the dropdown?
Yes. FILTER and XLOOKUP are dynamic array formulas and recalculate automatically when the referenced dropdown cells change.
Can I use these formulas in an older .xls workbook?
FILTER and XLOOKUP work best in current WPS Spreadsheets and .xlsx files. Save older workbooks as .xlsx to use them reliably.
How do I create the Division and Month dropdown lists?
Select the cell, go to Data > Data Validation, choose List, and enter the unique Division or Month values or select their range.
