Google-sheets – How to prevent cut-and-paste data from changing collapsed grouped cells

The =$A$1 notation can also be automatically moved if the value in (say) A1 is changed.

The only surefire solution is to enclose all your references in INDIRECT, e.g. =INDIRECT("$A$1") or even just =INDIRECT("A1").

The difficulty with either of these solutions is where you have many of the same formula, e.g.

=A1
=A2
=A3
...
=A99

Normally, you would fill in the first =A1 and then copy-and-paste (or drag down using autofill) to fill out the consecutive formula automatically. If, however the first formula is =$A$1 or =INDIRECT("A1") then this trick won't work (in fact, this is what $ is for - to prevent it being automatically changed).

If you have many consecutive formula, one solution is:

1. Create the formula as you normally (use plain =A1 and autofill)
2. Use the replace dialog (Ctrl+H) and use "search in formulas" to wrap all =A1, =A2 ... =A99 in the =INDIRECT("A1") etc. Note you have the option to use regular expressions when doing a find-and-replace.

The following Google Apps Script (GAS) will achieve your goal automatically:

function CUMSUM(array) {  
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data (2D array)
  var aTest = sh.getRange(array).getValues();

  // convert 2D array to 1D array
  var aData = [].concat.apply([],aTest);

  // create new array to hold data
  var aResult = new Array(); 

  // itterate through data range  
  for(var i=0, len=aData.length; i<len; i++) {
    if(i==0) {
      aResult[i] = aData[0];
    } else {
      aResult[i] = aResult[i-1] + aData[i];
    }
  }

  // pass result to formula 
  return aResult;  
}

Add this script, via the script editor, to your spreadsheet and the function CUMSUM is available throughout the worksheet, like so: =CUMSUM("A2:A8"). Because an array is returned, use TRANSPOSE to get the right positioning.

See example file I've created: running total