WPS Spreadsheet: Use FILTER to Return Rows from Another Column
Question details
The user needs to create a refreshable table on another worksheet that displays rows meeting a condition in a separate source column.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A source table contains records and a condition column such as Status. The user wants to enter a criterion and return matching rows to another worksheet.
- Observed behavior
- The goal is for the destination table to display all matching rows and update automatically when the source data changes.
Confirm that the source range has a header row and a clearly named condition column, such as Status. On the destination worksheet, reserve a blank area for the returned rows and enter the criterion in a cell such as A2.
Use FILTER with the source table and condition column
Use this method in a WPS Spreadsheet version that supports dynamic array formulas and structured table references.
The FILTER function returns every source row whose condition matches the selected criterion. Because the result spills into nearby cells, leave the destination area clear so new rows can appear automatically.
Open the worksheet where you want the matching records to appear, then select the first cell of the output area, such as B2.
Type =FILTER(SourceTable,SourceTable[Status]=A2,"No matches") in the formula bar, replacing SourceTable, Status, and A2 with your table name, condition column, and criteria cell.
Press Enter. WPS Spreadsheet spills the matching rows into the cells below and across from the selected cell.
Change a value in the source table or update the criterion in A2. The returned table refreshes automatically when the source data changes.
Filter a regular range when table references are unavailable
Use this alternative when the workbook uses ordinary cell ranges or your WPS Spreadsheet version does not recognize structured table references.
Create a Refreshable Filtered Table in WPS Spreadsheet
WPS Spreadsheet can use the FILTER function to return matching records to another worksheet. Enter a table-based or range-based formula, and the spilled results update when the source data or criteria changes.
- 1. Prepare the source data: Open WPS Spreadsheet and confirm that the source data has headers, a condition column, and a defined table or usable cell range.
- 2. Set the criterion: On the destination worksheet, enter the value to match, such as Pending or Complete, in a cell such as A2.
- 3. Insert the FILTER formula: Select the destination cell and enter =FILTER(SourceTable,SourceTable[Status]=A2,"No matches"), or use a range-based formula if the data is not formatted as a table.
- 4. Confirm the spilled results: Press Enter and leave the cells around the destination cell empty so WPS Spreadsheet can display all matching rows.
- 5. Update the source or criterion: Edit the source table or change the value in A2 to verify that the filtered results refresh automatically.
Frequently Asked Questions
Which WPS Spreadsheet formula returns rows that match a value in another column?
Use the FILTER function, such as =FILTER(SourceTable,SourceTable[Status]=A2,"No matches"). Replace the table name, condition column, and criteria cell with your own references.
Can FILTER return results on a different worksheet?
Yes. Enter the FILTER formula on the destination worksheet and reference the source table or range on the source worksheet. For example, use =FILTER(SourceSheet!A2:D100,SourceSheet!C2:C100=A2,"No matches").
Why does WPS Spreadsheet show a spill or blocked-result error?
The cells needed for the filtered results may not be empty. Clear the cells below and beside the formula, then confirm that the destination area has enough space for the returned rows.
Will the filtered table update when I add or edit source records?
It updates automatically when the formula references an Excel-style table, such as SourceTable. A fixed range updates only within the referenced rows, so expand the range when adding records outside it.
What should I do if WPS Spreadsheet does not recognize structured table references?
Use ordinary worksheet ranges instead, such as =FILTER(SourceSheet!A2:D100,SourceSheet!C2:C100=A2,"No matches"). Check that your WPS Spreadsheet version supports the FILTER function and dynamic arrays.
