How to Automatically Update a Customer Sheet from an Excel Template in WPS Office
Question details
The user needs to update a customer sheet automatically from an Excel template that contains item descriptions, prices, and changing rows.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Maintaining a customer worksheet that reflects current item data as the source template changes.
- Observed behavior
- The customer sheet should show current item descriptions and prices even when template rows are added, deleted, or reordered.
Open the template and customer sheet in WPS Office Spreadsheets and identify a unique item code column. If your data has no unique identifier, add one before creating formulas or tables.
Create an Excel Table and Use Lookup Formulas for Automatic Syncing
Use this method when the template rows change frequently and you need the customer sheet to reflect added, deleted, or reordered items automatically.
Formatting the template as an Excel Table gives formulas a structured reference that expands and contracts with the data. Combining the table with INDEX and MATCH keeps the customer sheet linked by item code rather than by fixed cell position.
Open the template in WPS Office Spreadsheets. Select the data range that contains the item code, description, and price columns, including the header row. Go to the Insert tab and click Table; in the dialog, enable My table has headers and click OK.
With the table selected, go to the Table Tools tab or use the Name Box, then enter a clear table name such as ItemTable.
In the customer worksheet, add columns for Item Code, Description, and Price. Enter the item codes for the products the customer needs.
In the Description cell next to the first item code, enter a formula such as =IFERROR(INDEX(ItemTable[Description],MATCH(A2,ItemTable[Item Code],0)),""). Drag the fill handle down and adapt the formula for the Price column.
Link Individual Cells for a Single Price or Description
Use this lighter approach only when you need to mirror one fixed cell and the template layout will not change.
Synchronize Customer Sheets Directly in WPS Office Spreadsheets
Use WPS Office Spreadsheets to format the template as an Excel Table, add lookup formulas, and maintain a customer sheet that updates when item descriptions, prices, or rows change.
- 1. Format the template as a table: Open the template in WPS Office Spreadsheets, select the data range, click Insert > Table, and confirm the header row.
- 2. Add unique item codes: Create a customer sheet with item codes that match the template's unique identifier column.
- 3. Build lookup formulas: Use INDEX and MATCH or VLOOKUP formulas to pull current descriptions and prices from the table.
- 4. Update the table data: Add, edit, or delete rows in the template; WPS Office recalculates and the customer sheet shows current values.
Frequently Asked Questions
Can I use a simple cell reference to automatically update my customer sheet?
A simple cell reference such as ='Template'!B2 can update one fixed value. If the template rows are inserted, deleted, or sorted, use an Excel Table and a lookup formula based on item codes so the customer sheet keeps pointing to the correct item.
How do I prevent #N/A errors when an item is removed from the template?
Wrap the lookup formula in IFERROR. For example, use =IFERROR(INDEX(ItemTable[Description],MATCH(A2,ItemTable[Item Code],0)),"") to return a blank cell instead of an error.
Will this table and formula method work if I later open the file in Microsoft Excel?
Yes. The .xlsx workbook, Excel Table, and lookup formulas are compatible with Microsoft Excel, so you can switch between WPS Office and Excel without rebuilding the template.
Can WPS Office refresh the customer sheet automatically without formulas?
For a formula-free refresh, you can use the Data tab's From Table/Range or Power Query features if available in your WPS Office version. For most template updates, the Excel Table plus lookup formula method is the simplest reliable option.
