1

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.

3
  • 1
    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
    Jan 19, 2017 at 2:38
  • I changed the spreadsheet to use :A, but the initial $2 is important, and still changes.
    – fishybell
    Jan 20, 2017 at 18:55
  • I was trying to say I could still needed to use A$2:A rather than A:A.
    – fishybell
    Apr 17, 2017 at 16:31

2 Answers 2

1

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.

0

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

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

  // grab data
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  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); }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.