I'm having a problem with my budget spreadsheet. For simplicity's sake, I can recreate the problem as follows.
I have two columns, A with a list of numbers, and B with several cells referencing a block of cells column A. For example:
A B
----------------------------------------
1. | 6 | =sum(A$1:A$100) |
2. | 16 | =average(A$1:A$100) |
3. | 12 | |
4. | 10 | |
5. | | |
6. | | |
My problem is if I drag and drop (or cut and paste) cells A1:A4 and move them to A3:A6 my equations in column B change to match, as such:
A B
----------------------------------------
1. | | =sum(A$3:A$103) |
2. | | =average(A$3:A$103) |
3. | 6 | |
4. | 16 | |
5. | 12 | |
6. | 10 | |
If I use copy/paste the values of column B won't change, but then I run into the problem of the contents of the original A column do change (ie. if the contents of A1 were "=C1" then it would change to "=C3" in the example).
In my actual spreadsheet I'm moving blocks of ledger data down to accommodate new data from my bank. I'm moderately sure this wasn't what was happening a month ago when I last did my budget, or the time before, or the time before.
Best Answer
You can get round this by using an indirect reference. For example:
The reference will then always refer to the specified range even when data is moved through drag and drop or cut and paste.