Keep a Linked Excel Copy With Private Notes in WPS Spreadsheets
Question details
The user needs to maintain current data from a shared workbook while keeping private comments safely linked to the correct record, even when the source is sorted or changed.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a shared workbook that is updated or sorted by multiple users, and needing a private notes layer that stays aligned with the correct records.
- Observed behavior
- Directly linking or copying an entire spreadsheet can cause private notes to shift to the wrong row when the source data is sorted or changed.
Before building the linked copy, open the shared workbook in WPS Spreadsheets and confirm every record has a stable unique identifier, such as Record ID, employee number, or SKU. If no unique column exists, add one first; otherwise notes cannot be matched reliably when the source is sorted or changed.
Store Private Notes in a Separate Table and Link by Unique Record ID
Use this to keep private notes on a separate sheet or workbook and display them in the current data view with XLOOKUP, so sorting the source never moves your notes to the wrong record.
Direct cell comments can shift when rows are sorted because they are anchored to the cell position instead of the record. A separate notes table keyed by a stable ID keeps each note tied to the correct record regardless of row order.
Open the shared workbook in WPS Spreadsheets and make sure each record has a stable unique ID column, such as Record ID. If not, add one manually or with a formula before sharing.
Add a new sheet named Private Notes in your linked copy or a separate notes workbook, and create columns: Record ID, Note, Author, and Date.
In the Private Notes sheet, enter each note on the row whose Record ID matches the original record, not by row number.
In the linked copy, insert a Notes column and use =XLOOKUP([@RecordID], 'Private Notes'!A:A, 'Private Notes'!B:B, "") to return the private note for that Record ID. Adjust sheet and column letters to match your workbook.
When the source data is sorted or changed, the XLOOKUP continues to follow the Record ID, keeping each private note on the correct record in your linked copy.
Keep Shared Data Current and Private Notes Aligned in WPS Spreadsheets
Use a stable Record ID and a separate private notes table in WPS Spreadsheets. Link the current data with XLOOKUP so sorting, refreshing, or sharing the source file does not move private comments to the wrong record.
- 1. Open the shared workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the shared .xlsx workbook. Confirm each record has a stable unique ID column before making the linked copy.
- 2. Create a separate Private Notes sheet: Add a sheet named Private Notes and set up columns: Record ID, Note, Author, and Date. Enter each note on the row matching its Record ID.
- 3. Build the linked copy and add the Notes column: In your linked copy, add a Notes column and enter =XLOOKUP([@RecordID],'Private Notes'!$A:$A,'Private Notes'!$B:$B,"") to pull the matching private note.
- 4. Refresh data and verify alignment: After sorting the source or refreshing the linked copy, check that notes still appear next to the correct Record ID. The lookup follows the ID, not the row position.
Frequently Asked Questions
Why do my notes shift to the wrong rows after sorting?
Notes or comments attached to cell positions move with the row when the source is sorted. Use a stable unique Record ID and a separate notes table, then link notes with XLOOKUP or INDEX/MATCH so they always follow the correct record.
Can I keep private notes in the shared workbook so only I see them?
Keeping truly private notes inside a shared workbook is risky because sheet visibility, cell comments, and formatting can be exposed or shifted. A separate notes workbook or sheet with limited access is safer, and the linked copy displays only the notes you choose through the Record ID lookup.
Can I use XLOOKUP in WPS Spreadsheets?
Yes, current versions of WPS Spreadsheets support XLOOKUP. If your version does not have XLOOKUP, use =VLOOKUP(RecordID,'Private Notes'!$A:$D,2,FALSE) or =INDEX('Private Notes'!$B:$B,MATCH(RecordID,'Private Notes'!$A:$A,0)) as an alternative.
How do I update shared source data without overwriting my notes?
Keep the source data in a query/import area or linked copy and store notes in a separate sheet outside the imported range. When you refresh the source data, only the data range updates; the notes table remains intact and is matched by Record ID.
What columns should the private notes table include?
At minimum include Record ID, Note, Author, and Date. You can add extra columns such as Status or Follow-up if they help your workflow, but the key is that Record ID matches the source exactly.
