How do I stop a static cell refrence from changing, when adding a new row?-CodePudding

I have a document I am using to calculate finances. I have the following working script that inserts a new row above row 2 pushing all data at row 2 and below down when cells A2:B2 are not blank.

function AddaLine() {
  var spreadsheet = SpreadsheetApp.getActive();
  var [a2, b2] = spreadsheet.getActiveSheet().getRange("A2:B2").getDisplayValues()[0];
  if (a2 != "" && b2 != "") {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('2:2').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('A2').activate();
  }
}

Cell A1 contains:

=SUM($A$2:A3)

I want A2 to remain static but A3 to incriment as new rows are automatically added. The problem I'm having is the static cell reference is incrementing when a new row is added and becomes

=SUM($A$3:A4)

I want it to go to

=SUM($A$2:A4)

only incrementing the second, non-static, reference cell each time new rows are inserted above row 2.

$A$2:A5
$A$2:A6
$A$2:A7
...
$A$2:A5000
...

Any help figuring out what I assume is a simple issue that I can't wrap my head around would be greatly appreciated!

NOTE: I am trying to do this in such a way that the automation works in a cellphone app as well as PC.

CodePudding user response：

Instead of =SUM($A$2:A3) try

=sum(offset(A3,-row(A3) 2,,row(A3)-1))

CodePudding user response：

INDIRECT or INDEX could make a static reference:

=SUM(INDIRECT("A2"):A3)

=SUM(INDEX(A:A,2):A3)