Automatically Populate Category Worksheets from a Master Table in WPS Spreadsheet
Question details
Create category-specific worksheets that automatically display matching rows from a master data list.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants each category to appear on its own worksheet while new or edited source rows are reflected automatically.
- Observed behavior
- Each worksheet should show the master-table headers and all records whose Category value matches that worksheet's category.
Make sure the master list has one header row and includes a column named Category. Use a current version of WPS Spreadsheet that supports dynamic-array functions such as FILTER and VSTACK.
Use VSTACK and FILTER with a named master table
Use this method when the source list is maintained as a table and each category worksheet needs a live, spill-based view of matching records.
The formula places the master table headers above the filtered records. It expands automatically when matching rows are returned, so keep the spill area clear of typed values or merged cells.
On the master worksheet, select the full data range, including headers, then use Insert > Table. Confirm that My table has headers is selected, and name the table MasterData from the table name box or Table Tools.
In the MasterData header row, confirm that the category field is named Category. The formula uses this exact structured reference: MasterData[Category].
Open the worksheet for the first category, select cell A1, and enter =VSTACK(MasterData[#Headers],FILTER(MasterData,MasterData[Category]="First Category","No records")). Replace First Category with the category value to display.
Create or open each category worksheet, select A1, paste the formula, and replace the category text with that worksheet's required category. Leave the cells below and to the right empty for the spilled results.
Filter a regular source range by worksheet category
Use this alternative when the master list is not formatted as a table and the source data is stored on a worksheet named Master List.
Build self-updating category worksheets with WPS Spreadsheet
WPS Spreadsheet can use FILTER and VSTACK formulas to create separate category views from one master table. Enter a dynamic-array formula on each destination sheet, and the displayed records update as the source table changes.
- 1. Create the master data table: Select the source list in WPS Spreadsheet and choose Insert > Table, then assign the table name MasterData.
- 2. Prepare a category worksheet: Add a worksheet for a category and select the top-left output cell, such as A1.
- 3. Enter the dynamic formula: Type =VSTACK(MasterData[#Headers],FILTER(MasterData,MasterData[Category]="First Category","No records")) in A1.
- 4. Set each worksheet category: Copy the formula to other category worksheets and change First Category to the relevant category value.
Frequently Asked Questions
Why does my FILTER formula show a spill error on the category worksheet?
The formula needs empty cells for all returned rows and columns. Clear values, remove merged cells, or move the formula from its starting cell, such as A1, to an open area.
Can I use a cell reference instead of typing a category name in the formula?
Yes. Put the category name in a cell, such as B1, then use =VSTACK(MasterData[#Headers],FILTER(MasterData,MasterData[Category]=B1,"No records")). Changing B1 updates the returned records.
What happens when no records match the category?
The third FILTER argument controls this result. In the example formula, "No records" appears when MasterData contains no rows with the specified Category value.
Do new rows appear automatically after I add them to the master table?
Yes. If the source is an Excel-style table named MasterData and the new row is added within the table, the structured references expand and the FILTER result recalculates.
Can I keep the formulas when sharing the workbook with Excel users?
Yes, save the workbook as .xlsx. The recipient needs a version of Microsoft Excel that supports the dynamic-array functions used by the workbook, including FILTER and VSTACK.
