How to Link Excel Rows Based on Available or Not Available Status in WPS Office
Question details
The user needs to link rows from one worksheet to another in WPS Spreadsheets only when a status column contains AVAILABLE or NOT AVAILABLE, while avoiding unwanted zeros for blank cells.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a source worksheet with a status column and wants a clean destination list containing only matching AVAILABLE or NOT AVAILABLE rows.
- Observed behavior
- Reference or lookup formulas may return unwanted zeros for blank cells, or the user needs a reliable way to link only the required status rows.
Before you start, confirm that the source data has a consistent status column with values such as AVAILABLE and NOT AVAILABLE, and note the exact sheet name and range you want to link.
Use the FILTER function to link only AVAILABLE or NOT AVAILABLE rows
Best for a dynamic link from one worksheet to another. The FILTER function spills matching rows, and a wrapping IF statement removes unwanted zeros from blank returned cells.
This method is dynamic: when source statuses change, the linked rows update automatically. The formula checks a status column and returns all columns from the matching rows.
Open WPS Office Spreadsheets, click the destination worksheet tab, and select the first cell where the linked rows should start.
In the formula bar, enter a formula similar to: =IF(FILTER(Sheet1!A2:E100,(Sheet1!C2:C100="AVAILABLE")+(Sheet1!C2:C100="NOT AVAILABLE"),"")=0,"",FILTER(Sheet1!A2:E100,(Sheet1!C2:C100="AVAILABLE")+(Sheet1!C2:C100="NOT AVAILABLE"),"")) and adjust Sheet1, A2:E100, and C2:C100 to match your workbook.
Press Enter. WPS Spreadsheets fills the matching rows automatically; only rows whose status column is AVAILABLE or NOT AVAILABLE appear.
If any blank source cells still appear as 0 in the results, verify that the IF wrapper compares the FILTER result to 0 and returns an empty string for those cells.
Filter the source table in place and copy only the status rows you need
Use this when you need a quick one-time output rather than a dynamic link. WPS Spreadsheets AutoFilter or an Excel Table filter can isolate AVAILABLE and NOT AVAILABLE rows.
Return Excel Rows by Status in WPS Office Without Zeros
WPS Spreadsheets includes the FILTER function and table tools to pull only rows marked AVAILABLE or NOT AVAILABLE into another worksheet. Use the dynamic FILTER method to keep the linked list updated and wrap it with IF to display blanks instead of zeros.
- 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheets and open the workbook that contains the source status data.
- 2. Select the destination cell: Click the worksheet tab where you want the linked rows to appear, then click the first blank cell for the result area.
- 3. Build the FILTER formula with an IF wrapper: Enter =IF(FILTER(Sheet1!A2:E100,(Sheet1!C2:C100="AVAILABLE")+(Sheet1!C2:C100="NOT AVAILABLE"),"")=0,"",FILTER(Sheet1!A2:E100,(Sheet1!C2:C100="AVAILABLE")+(Sheet1!C2:C100="NOT AVAILABLE"),"")) and adjust Sheet1, A2:E100, and C2:C100 to your data.
- 4. Apply the dynamic array result: Press Enter to spill the matching rows, then confirm that only rows with the selected statuses are listed.
- 5. Save as .xlsx: Go to Menu > Save As and choose Excel Workbook (*.xlsx) to keep full compatibility with Microsoft Excel.
Frequently Asked Questions
Which function in WPS Spreadsheets returns rows based on a status value?
Use the FILTER function. For example, set the include argument so the status range equals AVAILABLE or NOT AVAILABLE, such as (status_range="AVAILABLE")+(status_range="NOT AVAILABLE"). This returns the matching rows onto the destination sheet.
How do I stop blank cells from becoming zeros in the linked rows?
Wrap the result in an IF test that returns an empty string when the returned value is zero, such as =IF(FILTER(...)=0,"",FILTER(...)). This replaces unwanted zeros with blank cells.
Will the linked rows update automatically when the source status changes?
Yes. The FILTER function is dynamic. When you edit a status in the source range, the destination formula recalculates and updates the list automatically if the workbook supports dynamic arrays.
Can I link rows without changing my current Microsoft Excel files?
Yes. WPS Office Spreadsheets opens and saves .xlsx workbooks and supports the same FILTER function behavior, so you can switch between WPS Office and Microsoft Excel while keeping formulas intact.
