How to reconnect data based on daily shifting/updated cell values in Excel?

I am currently importing data into columns A-M that updates the data from the previous day. I currently have recorded a macro that deletes, sorts, and moves the data from the exported workbook (the new data) to my existing workbook (the old data). As a result, Columns A-M are updated, however, columns N-W remain the same. (Column N-W are additional data and progress updates).

The problem is, even though everything is working as it is supposed to, the updated data in columns A-M will sometimes add or remove rows based off the new information being imported. Additionally, some of the names of the documents are in multiple rows which causes some of the rows to swap with one another because I am sorting the excel by the document name (the other columns are all numbers essentially). This causes data in some of the N-W columns to be mismatched with their data in columns A-M.

Is there any way to automatically move the rows in column N-W so that they go back to their previous corresponding rows from columns A-M after updating the data in columns A-M.

Hi there,

Mi experience is that working with tables makes this whole sorting issue better. Then you can reference columns inside the same row and the references does not get screwed up.

The reference should then look somthing like this.

Sk?rmbild 2023-01-19 231941.png

On Worksheet (2) input
=XLOOKUP('Worksheet (1)'!$X2&'Worksheet (1)'!$E2, 'Worksheet (1)'!$A:$A&'Worksheet (1)'!$E:$E, 'Worksheet (1)'!O:O)

So, I partially solved my issue. Using XLOOKUP and multiple criteria to essentially recreate a separate worksheet and delete the previous one. I created a static DocNum in Column X and used the lookup array on 2 different identifiers. My only issue is that some documents just don't have any differentiating static identifiers to provide accurate results in worksheet 2. They have different currency values, but I can't rely on those not to change during the import/update stage.

Are the values in N-W lookups based on the values in A-M? or are they static values in the file.

If static, then the problem seem to be the update macro, you need then to better be able to identify the rows to update. Also if yes, the sorting should not be a problem.

If lookups, then converting the dataset to table and then update the lookups so that they have in-table references (@) the way I showed above....

I was not able to open your file, try attaching it again so I can have a closer look...