WPS Spreadsheets: Create Individual References from a Filtered Excel Table
Question details
The user needs to create a sortable list from filtered Excel table records and understand why each spilled result does not display a separate source-cell reference.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- An asset register contains outstanding service work, and the user wants to filter or sort the records while optionally retaining an individual reference for every destination cell.
- Observed behavior
- A dynamic-array formula returns one spilled range, so selecting an output result points to the formula in the anchor cell instead of showing an individual reference such as ='Sheet name'!B24.
Confirm that the source records have consistent column headers and that the intended output area is empty so the FILTER or SORT result can spill without a blockage. Convert the source range to a table if you want new or removed records to flow into the report automatically.
Use FILTER or SORT on an Excel Table for a sortable service report
Use this method when the report should update automatically as table records are added, removed, or changed.
A dynamic-array formula produces one spill range controlled by the formula in its top-left anchor cell. The displayed results are not separate formulas, so clicking an individual result does not create a source reference for that row.
For example, if the table is named AssetRegister and its status column is named Status, enter a formula such as =FILTER(AssetRegister,AssetRegister[Status]="Outstanding") in the report's first output cell. To sort the filtered results, wrap the formula with SORT or specify the desired sort column.
In WPS Spreadsheets, click any cell in the asset register, open the Insert tab, choose Table if the data is not already a table, and confirm that the table includes header rows.
Click inside the table and use the table properties or name box to confirm its name, such as AssetRegister. Verify the exact header used for the service status field, such as Status.
Select the first cell of the report area and enter =FILTER(AssetRegister,AssetRegister[Status]="Outstanding"), then press Enter. Leave the cells below and beside the formula empty so the result can spill.
To sort the report, replace the formula with a SORT-wrapped version such as =SORT(FILTER(AssetRegister,AssetRegister[Status]="Outstanding"),3,1), adjusting 3 to the column to sort and 1 for ascending order.
Create individual cell references with formulas or a helper column
Use this method when every destination cell must contain its own source reference, even though the list may require manual adjustment after records change.
Build Dynamic Filtered Reports or Individual Cell Links in WPS Spreadsheets
WPS Spreadsheets lets you organize asset records in a table, generate an automatically updating FILTER or SORT report, and create separate formulas when each destination cell needs its own source reference.
- 1. Create the table: Open WPS Spreadsheets, select the asset data, and choose Insert > Table to create a structured source range.
- 2. Generate the report: Select an empty report cell and enter a formula such as =FILTER(AssetRegister,AssetRegister[Status]="Outstanding") to return records needing service.
- 3. Sort the results: Wrap the filter with SORT, for example =SORT(FILTER(AssetRegister,AssetRegister[Status]="Outstanding"),3,1), and adjust the sort column number.
- 4. Add individual links when required: For cell-level references, select a destination cell and enter a direct formula such as ='Sheet name'!B24, then fill it down with Home > Fill > Down.
Frequently Asked Questions
Why does clicking a filtered result show only one formula?
FILTER and SORT return a dynamic-array spill range controlled by the formula in the anchor cell. Select the top-left cell of the spill range to view or edit the formula for the entire result.
Can a spilled range create a separate source reference for every row?
No. A spilled range is managed by one anchor formula. If every destination cell must contain an individual reference, enter separate formulas or use a helper column and fill the formulas down.
How do I filter only assets that still need service?
Place the source data in a table, select an empty report cell, and enter =FILTER(AssetRegister,AssetRegister[Status]="Outstanding"), replacing the table and status names with your actual names.
How can I sort the filtered service list?
Use SORT around FILTER, such as =SORT(FILTER(AssetRegister,AssetRegister[Status]="Outstanding"),3,1). Change 3 to the position of the column used for sorting and use -1 for descending order.
Will individual references update when source rows are deleted?
They may not adjust to the intended asset after rows are removed or reordered. Review the formulas after source changes, or use a structured table with FILTER and SORT when automatic report updates are more important.
