# Prevent dragging and dropping from changing cells referencing moved cells

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.

• Is the row number 100 important, or just "some large number"? If you want to sum everything in column A, use `sum(A:A)` and the problem goes away.
– user135384
Commented Jan 19, 2017 at 2:38
• I changed the spreadsheet to use :A, but the initial \$2 is important, and still changes. Commented Jan 20, 2017 at 18:55
• I was trying to say I could still needed to use A\$2:A rather than A:A. Commented Apr 17, 2017 at 16:31

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.

I ended up writing a custom script to move the data up and down:

``````function moveBlock(rowStart,colStart,colEnd,direction) {
// verify direction
if (direction == 0) {
'Error!',
'You programmed in an invalid direction!',
ui.ButtonSet.OK);
return false;
}

// grab data
var maxRow = sheet.getDataRange().getValues().length;

// move up or down
if (direction < 0) {
// up
var oVals = sheet.getRange(rowStart-direction,colStart,maxRow-rowStart-direction,colEnd-colStart+1).getValues();
var cData = sheet.getRange(maxRow,colStart,maxRow-direction,colEnd-colStart+1);
cData.clear();
var nData = sheet.getRange(rowStart,colStart,maxRow-rowStart-direction,colEnd-colStart+1);
nData.setValues(oVals);
} else {
// down
var oVals = sheet.getRange(rowStart,colStart,maxRow-rowStart+1,colEnd-colStart+1).getValues();
var cData = sheet.getRange(rowStart,colStart,direction,colEnd-colStart+1);
cData.clear();
var nData = sheet.getRange(rowStart+direction,colStart,maxRow-rowStart+1,colEnd-colStart+1);
nData.setValues(oVals);
}

return true;
}
``````

I call the script via images in the document that I've assigned scripts to. Since you can't assign a script with parameters, I ended up created several macro-like scripts. For each ledger there are 4 macros: one that moves the data up one row, down one row, up 5 rows, and down five rows. I don't have to pass in the sheet name as the script uses the active sheet.

``````// macros/shortcuts for the various sheets that use moveblock:
function moveUp_Ledger_CC() { return moveBlock(2,1,6,-1); }
function moveDown_Ledger_CC() { return moveBlock(2,1,6,1); }
function moveUp_Ledger_CC_5() { return moveBlock(2,1,6,-5); }
function moveDown_Ledger_CC_5() { return moveBlock(2,1,6,5); }
function moveUp_Ledger_MACU() { return moveBlock(2,1,8,-1); }
function moveDown_Ledger_MACU() { return moveBlock(2,1,8,1); }
function moveUp_Ledger_MACU_5() { return moveBlock(2,1,8,-5); }
function moveDown_Ledger_MACU_5() { return moveBlock(2,1,8,5); }
``````