Google Sheets – Prevent Dragging and Dropping from Changing Cell References

google sheetsgoogle-apps-script

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:

=sum(indirect("A$3:A$103")) 

The reference will then always refer to the specified range even when data is moved through drag and drop or cut and paste.