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:
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.