Use a Master Employee List to Update Excel Sheets Automatically
Question details
The user needs to maintain one editable employee list and use it as the single source for training registers, expiry sheets, and summary reports across a workbook.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages multiple workbook sheets that depend on employee information and wants to avoid manually updating the same list in every sheet.
- Observed behavior
- The goal state is a single master employee list that automatically propagates changes to dependent sheets and reports without manual re-entry.
Confirm that employee identifiers such as Employee ID, name, or email are identical in every sheet, and prepare a clean master list with one row per employee. If you plan to use Power Query or PivotTables, store the master data in a named Excel Table so new rows are included automatically.
Create a Master Excel Table and Link Dependent Sheets with Formulas
Use this when you want dependent training registers, expiry sheets, and reports to recalculate automatically after you edit the master list.
A named Excel Table expands automatically when employees are added or removed, so formulas that reference the table by name do not need to be re-selected each time.
For lookup fields, use VLOOKUP or INDEX/MATCH. For multi-cell results, use dynamic array formulas if supported by your WPS Office version.
Open the workbook in WPS Office Spreadsheets, select the master employee range including headers, click Insert > Table, and check My table has headers.
With a cell in the table selected, go to Table Tools > Design, open the Table Name box, and enter something like MasterEmployees.
In the dependent sheet, click the first data cell and enter a lookup formula such as =VLOOKUP(A2,MasterEmployees,2,FALSE) or =XLOOKUP(A2,MasterEmployees[EmployeeID],MasterEmployees[Surname]).
Fill the formula down and across the required columns, then add or remove an employee in the master table and check that the dependent sheet updates immediately.

Use Power Query to Create Refreshable Linked Sheets
Use this when you want a repeatable, query-based import from the master list into separate sheets with transformation and filtering.
Summarize Master Employee Data with PivotTables
Use this for summary reports such as counts by job title, department, or training status without editing formulas.
Use a Master Employee List to Update WPS Spreadsheets Automatically
WPS Office Spreadsheets can act as the single source of truth: create one master employee table, link dependent sheets with formulas or queries, and refresh summary reports without maintaining duplicate copies.
- 1. Download and open WPS Office Spreadsheets: Download WPS Office Free, open the workbook with the master employee list, and sign in if you want cloud sync.
- 2. Create a named master employee table: Select the employee data, click Insert > Table, check My table has headers, and name the table in Table Tools > Design.
- 3. Link dependent sheets: In each reporting sheet, use lookup formulas or Power Query connections to pull data from the master table instead of pasting static lists.
- 4. Refresh and review: Add, remove, or reorder employees in the master table, then click Data > Refresh All or confirm that formulas recalculate automatically.

Frequently Asked Questions
Can WPS Office Spreadsheets create a named Excel Table like Excel?
Yes. Select the employee range, go to Insert > Table, check My table has headers, and rename the table from Table Tools > Design > Table Name.
Will the dependent sheets update automatically when I add a new employee?
Formulas that reference the named table by column update automatically. Power Query and PivotTables need a refresh: open Data > Refresh All or right-click the PivotTable and choose Refresh.
What is the best formula to pull employee details from a master list?
Use VLOOKUP or INDEX/MATCH for a single value. If your WPS version supports XLOOKUP, use =XLOOKUP(A2,MasterEmployees[EmployeeID],MasterEmployees[JobTitle]).
Can I use this master list for training registers and expiry sheets?
Yes. Create a master table and then link each training or expiry sheet by employee ID using lookup formulas or Power Query, so changes flow through after refresh.
What should I do if my WPS version does not have Power Query?
Use formula-based solutions with named Excel Tables and VLOOKUP or XLOOKUP. They are available in WPS Office Spreadsheets and update without refreshing queries.




