How to Display Rows for a Selected Date on Another Worksheet in WPS Office Spreadsheets
Question details
The user needs to extract every row from a master data sheet that matches a date entered on an analysis sheet and have the results update automatically.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user maintains master data on one worksheet and wants to enter a date on an analysis worksheet to display all matching rows below that date cell.
- Observed behavior
- The matching rows should appear automatically and refresh whenever the selected date changes.
Prepare a source worksheet with dates in one column and an analysis worksheet with a date input cell. Make sure your version of WPS Office supports the FILTER function and dynamic arrays.
Use the FILTER Function to Return All Rows Matching the Selected Date
Use this solution when you want a simple formula that returns every matching row and refreshes automatically when the date cell changes.
The FILTER function extracts rows from a source range based on a condition. In this example, the condition compares the date column on Sheet1 to the date entered in Sheet2!B1, so no sorting or manual refresh is required.
Open the WPS Office workbook, go to the analysis worksheet such as Sheet2, click cell B1, and type the date you want to filter by.
Click cell A3 on the same analysis worksheet where the returned rows should start.
Type =FILTER(Sheet1!$A$2:$D$12,Sheet1!$A$2:$A$12=B1) and press Enter. Replace $A$2:$D$12 with your actual source data range and $A$2:$A$12 with the source date column.
Change the date in B1 and confirm that WPS Spreadsheets recalculates and updates the returned rows automatically.
Display Matching Rows by Date in WPS Office Spreadsheets
Open WPS Office Spreadsheets and use the FILTER function on an analysis sheet to return every row from a master data sheet that matches the selected date. The results update automatically whenever the date input changes.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the master data and analysis sheets.
- 2. Enter the lookup date: Click cell B1 on the analysis worksheet and type the date you want to display.
- 3. Insert the FILTER formula: Click cell A3, enter =FILTER(Sheet1!$A$2:$D$12,Sheet1!$A$2:$A$12=B1), and replace the example ranges with your actual data.
- 4. Confirm the dynamic results: Press Enter to see the matching rows, then change the date in B1 to confirm that the results update automatically.
Frequently Asked Questions
Does the FILTER function work in WPS Office Spreadsheets?
Yes. Current versions of WPS Office Spreadsheets support the FILTER function, so you can use the same formula structure as in Excel and the results will spill automatically.
Will the displayed rows update when I change the date?
Yes. Because the formula references the input cell B1, WPS Spreadsheets recalculates automatically and refreshes the matching rows whenever the selected date changes.
What does the #SPILL! error mean?
It means one or more cells in the output area are blocked. Clear the cells directly to the right and below the formula cell so WPS has room to return the full result.
Can I use a larger source data range?
Yes. Replace the example ranges in the formula with your actual data range and make sure the date column range has the same number of rows as the data range.
Do I need to sort the source data by date?
No. The FILTER function returns all matching rows regardless of the source data order, so sorting is not required.
