Saturday, March 25, 2017

microsoft excel - How to keep references unchanged after sorting

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


enter image description here


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.

No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...