Function Problems

Use an Excel Drop-Down to Display Different Sales Tables in WPS Office

Phi Hung Vo Nov 13, 2026 869 views

Question details

The user wants to create a drop-down selector in a spreadsheet so that choosing a value displays the corresponding sales combination or sales table data on a summary sheet.

Use an Excel Drop-Down to Display Different Sales Tables in WPS Office

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: Maintaining a summary sheet that needs to switch between multiple sales tables or combinations without manually copying data.
Observed behavior: A drop-down selection should control the displayed sales records and return the matching rows from a source table.

Before you start

Make sure all sales records are in one structured table or named range with a column for the selection value, because consolidating source data makes the drop-down lookup easier to maintain.

Solution 1Recommended

Create a Data Validation Drop-Down and Return Matching Records with FILTER

Use this method when you have a consolidated sales table and want a selector cell to display matching rows automatically.

Structured Excel Tables and dynamic array functions reduce the need to create many separate sheets. If FILTER is not available, use XLOOKUP or CHOOSE for one-row or fixed returns.

Prepare a consolidated sales table

Enter all sales combinations in one source table, for example SalesTable, and include a column such as Combination for the selector values.

Create the drop-down selector cell

Select the summary sheet cell B2, go to the Data tab, click Data Validation, choose List, and set the source to the unique combination values such as =$G$2:$G$5.

Return matching records with FILTER

In the first output cell, enter =FILTER(SalesTable,SalesTable[Combination]=$B$2,"No matching records") and press Enter to spill the matching rows.

Test the selection

Change the drop-down value in B2 and confirm that the summary area updates to the corresponding sales rows.

Create a Data Validation Drop-Down and Return Matching Records with FILTER

Best practice: Keep all combinations in one source table and use dynamic-array functions where available so the summary updates automatically.

Solution 2

Use CHOOSE with Named Ranges for Separate Sales Tables

Use this when each sales table is stored as a separate named range and you only need to return a fixed table area.

WPS Spreadsheets direct solution

Create a Sales Table Switcher in WPS Office Spreadsheets

Use WPS Office Spreadsheets to set up a data validation drop-down and FILTER formula that displays the selected sales table on your summary sheet without rebuilding the report.

1. Open your workbook in WPS Office Spreadsheets: Start WPS Office Spreadsheets, then open the .xlsx sales workbook or import the existing Excel file.
2. Build the source sales table: Place all sales combinations in one structured table, for example SalesTable, and include a column named Combination.
3. Create the selector drop-down: Select cell B2 on the summary sheet, go to the Data tab, click Data Validation, choose List, and set the source to the unique combination values.
4. Enter the display formula: In the first output cell, enter =FILTER(SalesTable,SalesTable[Combination]=$B$2,"No matching records") and press Enter.
5. Save and test: Change the drop-down value, confirm the summary updates, then save the workbook as .xlsx to keep Excel compatibility.

Switch sales tables from a single drop-down without maintaining many separate summary sheetsOpens, edits, and saves .xlsx Microsoft Excel workbooks with compatible formulas and formattingUses familiar Data Validation, FILTER, and Table features for easy maintenanceFree, lightweight WPS Office interface that feels familiar to Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Can the drop-down control more than one table at once?

Yes. If the tables share the same structure, keep them in one consolidated table and use FILTER. If they are separate, use CHOOSE or INDIRECT with named ranges and make sure the selected value maps to the correct range.

Why does the FILTER formula return a #CALC! or empty result?

It usually means no rows match the selected value. Check that the drop-down cell B2 matches the Combination column exactly, including extra spaces or capitalization.

How can I add a new sales combination to the drop-down automatically?

Store the unique combinations in a spilled range or named range, then set Data Validation List source to that range reference, such as ='Summary'!$G$2#.

Does WPS Office support dynamic array functions like FILTER and XLOOKUP?

Current WPS Office Spreadsheets versions support dynamic array functions in many modern plans. If FILTER is not available in your version, use XLOOKUP or INDEX/MATCH as a fallback.

Can I save this drop-down report in Microsoft Excel format?

Yes. WPS Office Spreadsheets can save the workbook as .xlsx, so the drop-down, structured table, and formulas remain available in Microsoft Excel.

Excel & Data Problems