WPS Spreadsheet: Keep Excel Data Linked When Rows Move
Question details
The user needs to keep manually entered information associated with the correct product or record when rows change position between linked workbooks.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- One workbook references another, but rows in the Original workbook may be inserted, deleted, or moved while related information is manually maintained in an Edited workbook.
- Observed behavior
- Row numbers no longer reliably identify the same product or record, so manually entered data can become misaligned after the source workbook changes.
Add a stable, unique key column to the Original workbook, such as Product ID, and verify that every record has a nonblank key with no duplicates. Copy each key as a literal value into the corresponding key column in the Edited workbook before creating lookup formulas.
Use a stable key with XLOOKUP to reconnect moved rows
Use this method when records may be inserted, deleted, sorted, or moved and the Edited workbook must retrieve values from the correct source record.
Row positions are temporary, but a unique product or record key remains associated with the same record. Store that key in both workbooks and use it as the lookup value instead of matching row numbers.
For example, if the key is in column A and the source value is in column D, the Edited workbook can retrieve the matching value with XLOOKUP even after the Original workbook is reordered.
In the Original workbook, open the source sheet and add a header such as Product ID in column A. Enter a unique, stable value for every record, then use the Data tab's duplicate-checking or sorting tools to confirm that the keys are not repeated.
Open the Edited workbook, add the same Product ID header, and use Paste Special > Values to place a literal copy of each key in the key column. Do not use the row number as the key.
Select the destination cell in the Edited workbook and choose Formulas > Insert Function, or type a formula such as =XLOOKUP($A2,'[Original.xlsx]Sheet1'!$A:$A,'[Original.xlsx]Sheet1'!$D:$D,"Not found") using the correct workbook, sheet, key column, and return column.
Press Enter, check the returned value, then drag the fill handle down or use Home > Fill > Down to apply the lookup to the remaining records. Keep the key reference in column A and adjust the return column for each related field.
In the Original workbook, insert a row or sort the records from the Data tab. Recalculate or reopen the linked files, then confirm that each Edited workbook value still follows its Product ID rather than its former row number.
Use INDEX and MATCH when XLOOKUP is unavailable
Use this fallback when the installed WPS Spreadsheet version or an older Excel-compatible workbook does not support XLOOKUP.
Keep linked workbook records aligned with WPS Spreadsheet lookup formulas
WPS Spreadsheet lets you create stable record keys and use XLOOKUP or INDEX-MATCH to retrieve related values across workbooks. Because the formula matches the key instead of the row number, inserted, deleted, sorted, or moved rows remain connected to the correct record.
- 1. Open both workbooks: Launch WPS Office and open the Original workbook and the Edited workbook in WPS Spreadsheet.
- 2. Add matching record keys: Create a unique Product ID or combined record key in the Original workbook, then paste the same keys as values into the Edited workbook.
- 3. Insert the lookup formula: In the Edited workbook, select the target cell and use Formulas > Insert Function or enter an XLOOKUP formula that searches the source key column and returns the required field.
- 4. Fill and verify the results: Copy the formula down, then insert or sort a test row in the Original workbook to confirm that the returned information follows the stable key.
Frequently Asked Questions
Why do row-based links break when rows are moved?
A row number identifies a position, not a permanent record. When rows are inserted, deleted, or sorted, that position can point to a different product. A unique key such as Product ID remains tied to the correct record.
Where should the stable key be stored?
Store the key as a dedicated column in both workbooks. In the Original workbook, it identifies each source record; in the Edited workbook, it should be pasted as a literal value so formulas can use it for matching.
Can I use product names instead of Product IDs?
Only if every product name is unique and does not change. If names can repeat or be edited, create a Product ID or combine multiple fields into a unique key before using XLOOKUP.
What formula can I use if XLOOKUP is not available?
Use INDEX and MATCH with an exact match, such as =IFERROR(INDEX('[Original.xlsx]Sheet1'!$D:$D,MATCH($A2,'[Original.xlsx]Sheet1'!$A:$A,0)),"Not found"). Update the workbook, sheet, key column, and return column references for your files.
