How to keep references unchanged after sorting

Question

In the following spreadsheet, the references will change after sorting and break the calculations.

Absolute referencing is not an option. The table has to be copied periodically to another sheet and placed it at a different row (the columns are identical). Absolute referencing will break the calculations after copying.

I would like to keep the references unchanged. One idea is to use formulas to maintain references. For example, S75 contains: =T70.
Change it into =(the address of the Count data cell)

What formulas could I use to achieve this?

If no formula could do, I need to resort to macro. Use absolute referencing ($T$70) first. Then run a macro which change all selected cells into relative referencing (T70) every time it needs to be copied. Only the reference of T70 needs to be changed, not any others. I don't know how to code. Could anyone help?

Thanks a lot.

Solution

You can use the OFFSET function to refer to cells in other rows.

You could add a column (you can hide it if you don't want to see it) that numbers the rows. For example enter the following formula in cell L72 and then copy it to the following rows.

=SUM(OFFSET(L72,-1,0),1)

Now change the formula in cell T76 to

=OFFSET(T76,-1-L76,0)+1.5

As the formula only refers to cells on its own row, it will still work after being sorted.

Comments

[masterton]

@MátéJuhász How could named range solve the problem? Remember the table has to be copied periodically to another sheet and placed it at a different row.

[masterton]

The ID column records the order of the data entered, the first data input being 1, the second being 2 and so on.

[masterton]

It won't work after you sort. The numbers will not be the same as the row number of the table after sorting.

[Blackwood]

You need to make sure that the column with the row numbers are not part of the data being sorted (so they remain where they are). I'll update the answer to suggest putting them in column L instead of M, so that there will be an empty column between them and the data.

[masterton]

Thank you for your reply. There are two issues of this solution. I have to re-enter the numbers when I add entries in between the table. There is an extra time cost to maintain them. I have to copy that column to another sheet periodically too. It is easy to forget it if I hide the column.

[Blackwood]

While it still takes some extra effort, what I would do if I expected to be inserting rows would be to replace the hardcoded 1, 2, 3,etc. with =SUM(OFFSET(L72,-1,0),1) in cell L72 and copy that formula to the rows below. Then rather than inserting a row, I would copy an existing row and replace the data in it.