How to Prevent #REF! Errors When Sheet References Change in WPS Office Spreadsheets
Question details
The user needs to keep a summary sheet synchronized with a source schedule sheet without getting #REF! errors when source cells are cut, pasted, or overwritten.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user keeps Sheet2 linked to a schedule on Sheet1 and updates Sheet1 by cutting, pasting, or overwriting data.
- Observed behavior
- Direct references such as =Sheet1!B1 change or become #REF! when source cells are moved, cut, or deleted.
Before editing or moving schedule cells, identify which formulas on other sheets use direct references such as =Sheet1!B1. Check whether Sheet1 has a stable identifier column, such as Task ID or Activity, that can be used for lookups instead of cell addresses.
Use XLOOKUP with a Stable Identifier to Replace Direct Sheet References
Use this when the schedule has a unique ID or label column and rows may be cut, pasted, or reordered.
Direct references such as =Sheet1!B1 follow cell positions. If those cells are cut or deleted, the formula loses its target and becomes #REF!. A lookup formula retrieves data by a stable label, so it survives normal edits to the source sheet.
In WPS Office Spreadsheets, open Sheet1 and confirm the schedule has a column with unique labels such as Task ID or Activity. If not, right-click the column header where you want the identifier, choose Insert, and type a unique label for each row.
Click the target cell in Sheet2, type =XLOOKUP($A2,Sheet1!$A$2:$A$100,Sheet1!$B$2:$B$100,"Not found"), and press Enter. Adjust $A2, the lookup range, and the return range to match your identifier and target column.
Select the cell with the formula, then drag the fill handle at the lower-right corner across or down to populate the other cells that should stay synchronized with Sheet1.
Use INDEX and MATCH Against a Fixed Table When XLOOKUP Is Not Available
Choose this if you are using an older workbook format or prefer INDEX and MATCH for compatibility.
Paste Special > Values When Updating the Source Schedule
Use this when you are moving or pasting data into Sheet1 and want to avoid overwriting or dragging formulas that break references.
Use Stable Lookups in WPS Office Spreadsheets to Prevent #REF! Errors
You can keep summary sheets synchronized in WPS Office Spreadsheets by replacing fragile direct references with XLOOKUP or INDEX/MATCH based on a stable identifier, and by using Paste Special > Values when updating source data. These steps preserve links without breaking formulas when rows are cut, pasted, or overwritten.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the Excel file containing Sheet1 and Sheet2.
- 2. Apply a lookup formula with a stable identifier: In Sheet2, enter =XLOOKUP($A2,Sheet1!$A$2:$A$100,Sheet1!$B$2:$B$100,"Not found") or use INDEX/MATCH, then press Enter.
- 3. Update source data without breaking references: When moving data, use Ctrl+C and right-click > Paste Special > Values, or keep the identifier column intact instead of deleting source cells used by formulas.
Frequently Asked Questions
Why does =Sheet1!B1 turn into #REF! after I cut and paste cells?
Cutting removes the original cell, so the formula can no longer point to that location and returns #REF!. Use a lookup based on a stable identifier or paste values instead of moving the referenced cells.
How do I use XLOOKUP to keep Sheet2 synchronized with Sheet1?
In Sheet2, select the result cell and type =XLOOKUP($A2,Sheet1!$A$2:$A$100,Sheet1!$B$2:$B$100,"Not found"), then press Enter. Replace the ranges to match your identifier column and the column you want to return.
Can INDEX and MATCH prevent #REF! errors?
Yes. Use =INDEX(Sheet1!$B$2:$B$100,MATCH($A2,Sheet1!$A$2:$A$100,0)) in Sheet2. It retrieves data by a lookup value instead of a fixed cell address, so it survives row moves and reordering.
When should I use Paste Special > Values?
Use it when you need to move or overwrite data on Sheet1 without pasting formulas or deleting cells that other sheets reference. Copy the data, right-click the destination, and choose Paste Special > Values.
Does this method work with Microsoft Excel files?
Yes. WPS Office Spreadsheets supports .xlsx files, XLOOKUP, INDEX/MATCH, and Paste Special, so you can edit and save Excel-compatible workbooks without losing formulas or formatting.
