Keep User-Entered Data Aligned After an Excel Table Refresh
Question details
Keep manually entered values connected to the correct records when an imported Excel table changes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- User-entered comments or other values must remain associated with imported records after a refresh or sort.
- Observed behavior
- Manual columns beside imported data can become misaligned when the source rows are refreshed, sorted, or replaced.
Identify a stable, unique field such as Job Number in the imported data, and check that the same identifiers are used for manual entries. Decide which columns you want to display on a separate combined sheet.
Link Manual Entries to Refreshed Data with XLOOKUP
Use a separate manual-entry table and a formula-driven sheet when your Excel version supports XLOOKUP and dynamic arrays.
Keep the imported table on its own sheet and name it SourceData using Table Design > Table Name. Ensure it contains columns named Job Number and Status.
On another sheet, enter Job Number and Comment headers, select the range, and choose Insert > Table. Name the table ManualEntries and enter one comment per Job Number.
On a new sheet, add Job Number, Status, and Comment headers in A1:C1. In A2, enter =FILTER(SourceData[Job Number],SourceData[Job Number]<>"",""). In B2, enter =FILTER(SourceData[Status],SourceData[Job Number]<>"","").
In C2, enter =XLOOKUP(A2#,ManualEntries[Job Number],ManualEntries[Comment],""). Refresh the imported table using Data > Refresh All; the combined sheet will display comments matched by Job Number.

Merge Imported Data and Manual Entries with Power Query
Use Power Query when you want to combine the two tables into a refreshable output table.
Match Refreshed Records and Comments in WPS Spreadsheet
Use a separate entry sheet and an exact-match VLOOKUP in WPS Spreadsheet to display comments alongside imported records without relying on row position.
- 1. Open the workbook: Open your workbook in WPS Spreadsheet. Keep imported Job Numbers in column A and statuses in column B on a sheet named Imported Data.
- 2. Store comments separately: Create a sheet named Manual Entries. Enter Job Numbers in column A and their comments in column B, starting in row 2.
- 3. Display the imported records: On a new sheet, enter Job Number, Status, and Comment in A1:C1. In A2, enter =IF('Imported Data'!A2="","",'Imported Data'!A2). In B2, enter =IF(A2="","",'Imported Data'!B2).
- 4. Look up and fill comments: In C2, enter =IF(A2="","",IFERROR(VLOOKUP(A2,'Manual Entries'!$A$2:$B$1000,2,FALSE),"")). Select A2:C2 and drag the fill handle down far enough to cover the expected records. Extend the lookup range if manual entries exceed row 1000.
- 5. Maintain the separate sheets: Update the imported data on Imported Data and enter comments on Manual Entries. Check the combined sheet to see each comment matched to its Job Number.

Frequently Asked Questions
Why do comments move to the wrong records after a refresh?
A refresh can add, remove, or reorder imported rows, while adjacent manual cells may stay in their existing positions. Store comments in a separate table keyed by Job Number instead of relying on row position.
What happens if a Job Number is duplicated?
XLOOKUP and VLOOKUP return the first matching entry, while a Power Query merge can produce multiple matches. Check both tables for duplicate Job Numbers and use a truly unique identifier where possible.
Where should I edit a comment after creating the combined view?
Edit it in the separate manual-entry table, alongside its Job Number. Formula results update from that table; for a Power Query output, use Data > Refresh All afterward.
Why is a matching comment blank?
Check that the Job Number exists in the manual-entry table and that both keys use consistent text or number formats. Also check for extra spaces and confirm that the lookup range includes the entry.




