Automatically Populate Employee Training Trackers in WPS Spreadsheets
Question details
The user wants to automatically populate position-specific employee training tracker sheets from a master employee list and keep employee information linked when the master list is sorted or expanded.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A trainer or HR administrator maintains a master employee list and needs separate training trackers by position that reflect changes without rebuilding each sheet.
- Observed behavior
- Trackers are not automatically populated or can become disconnected from employee information when the source list is sorted or expanded.
Before you start, set up a master employee list with one row per employee and consistent columns, such as Employee ID, Name, Position, Training, and Status. Keep the list in a normal worksheet range, and make sure the tracker sheets have empty spill areas below and to the right of the formula cell.
Use SORT and FILTER dynamic array formulas for position-specific trackers
Use this when your master employee list is a normal range and you want each position tracker to update automatically without manual copying.
Dynamic array formulas such as SORT and FILTER can spill a filtered employee list into the worksheet automatically. In WPS Spreadsheets, spilled arrays should be placed in a clear worksheet range rather than inside an Excel Table, because tables do not support spilled arrays in their body.
Reference the master list columns directly so that sorting, adding rows within the referenced range, or changing position names recalculates the tracker.
In WPS Spreadsheets, open the workbook and select the tracker sheet for a specific position, such as "Sales Associate".
Click the first blank cell under your tracker headers, for example A5, then type =FILTER(MasterList!A2:E200,MasterList!C2:C200="Sales Associate","No employees found"). Adjust the sheet name and column letters to match your master employee list.
Wrap the formula with SORT to keep the list in a stable order, for example =SORT(FILTER(MasterList!A2:E200,MasterList!C2:C200="Sales Associate","No employees found"),1,1). Press Enter and allow the array to spill.
Do not place the formula inside an Excel Table and do not enter data in the cells that the array needs to spill into. If you see a #SPILL! error, clear the blocking cells.
Next to the spilled results, use =XLOOKUP($A5,MasterList!$A$2:$A$200,MasterList!E$2:E$200,"Not found") or VLOOKUP to pull training status, completion date, and other employee information.
Use VLOOKUP or XLOOKUP to link individual employee rows without copying
Use this when you already have a fixed list of employee IDs on a tracker sheet and need to keep their training details connected to the master employee list.
Build auto-updating employee training trackers in WPS Spreadsheets
Use WPS Spreadsheets and dynamic array formulas to link position-specific training trackers to a master employee list, so employee information stays connected when the list is sorted or expanded.
- 1. Set up the master employee list: Open WPS Office Spreadsheets and create a master sheet with columns such as Employee ID, Name, Position, Training, and Status. Enter one employee per row.
- 2. Create a position-specific tracker sheet: Add a new worksheet for the position you want to track, and leave empty cells below the headers for the spilled results.
- 3. Enter the dynamic array formula: Click the first blank cell under the tracker headers and enter =SORT(FILTER(MasterList!A2:E200,MasterList!C2:C200="Sales Associate","No employees found"),1,1).
- 4. Press Enter and allow the array to spill: Press Enter to populate the tracker automatically. Do not place the formula inside a table or fill the spill area with other data.
- 5. Add lookup formulas for training details: Use =XLOOKUP($A5,MasterList!$A$2:$A$200,MasterList!E$2:E$200,"Not found") or VLOOKUP in adjacent columns to pull training status and completion dates.

Frequently Asked Questions
Can I put a FILTER formula inside an Excel Table?
No. Spilled array formulas such as FILTER and SORT are not supported inside an Excel Table body. Use a normal worksheet range for the spilled tracker, or reference the table from outside if the master data is formatted as a table.
Why do I get #SPILL! in WPS Spreadsheets after entering the formula?
#SPILL! means cells that the dynamic array needs are not empty. Clear the cells below and to the right of the formula, and do not place the formula inside a table.
How do I keep employee information connected when the master list is sorted?
Use formulas that reference the master list by Employee ID or by entire columns or range, such as FILTER, SORT, XLOOKUP, or VLOOKUP. Sorting the master list recalculates the formulas and keeps each employee's information together.
Does WPS Spreadsheets support SORT and FILTER dynamic arrays?
Yes. Current versions of WPS Spreadsheets support dynamic array functions in .xlsx workbooks. If these functions are not available, update WPS Office to the latest version.
Can I use Power Query instead of formulas for this tracker?
If your workbook tool supports Power Query, you can build a query to filter and load the master list. In WPS Spreadsheets, dynamic array formulas or lookup formulas are a direct formula-based alternative for the same result.




