If you have a table in Excel and have a reference somewhere to a cell in that table – let's say A7, and you insert a new row above so A7 will become A8, any reference to that cell gets updated so it now points to A8, as it should.
However, if you sort or filter the table so the content previously in A7 now is in A3, any reference to that cell is NOT updated. From my point of view, this seems like a major flaw in Excel, and I haven't found a way around it yet.
I have a source table with several columns of data entered manually that get's updated frequently and gets sorted and filtered all the time. Then I have a dependent table that automatically draws data from the source table for some columns, but also has columns with dates that are entered manually.
Whenever the source table gets sorted or filtered, the dependent table gets jumbled with the manually entered dates not appearing in the correct places, because data in the other columns has moved as a result of the sorting order and/or filter of the source table.
Can anyone offer a solution to this issue?
One workaround is to add a column labelled something like "OriginalOrder" and get that filled in and fixed (either just sequentially, or make it dynamic based on an order number or something else - basically, find a way to give each row a UUID) and then rather than using fixed references like "A7" use index(match()) functions to always go back to that same row UUID no matter where it is in your data.
I use this technique often when playing with data I'm developing algorithms on as I like to be able to sort the raw data multiple different ways for the sake of visualizations.
Edit - note that you can use VBA at the sheet level to add new constant UUIDs to rows upon entry of data in that row if you want to automate it slightly. The order of the UUIDs isn't critical, just that they are unique.