Google-sheets – When I insert a row, indirect does not update

formulasgoogle sheets

I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the formula doesn't update so I would have to update the formula each time in the summary sheet. Is there any way to fix this?

This is my formula now: =indirect(""&A33&"H44")
where A33 is referencing the sheet name and H44 is what I want to be updated when I add a row in the sheet being referenced. I hope that makes sense!

Best Answer

try like this:
```
=SUM(INDIRECT(A2&"!B5:B"))
```

or like this:
```
=SUM(INDIRECT(A2&"!B5:B"&ROW('S5'!B7)))
```
note: inserted row needs to be added before the last value in the range. eg. with this formula you will need to add row between B6 & B7 so the formula would rewrite itself to B8. if you add it after B7 the formula won't extend

Short Answer

Replace if( r.getColumn() != 5 ) by if( r.getColumn() != 5 && r.getRow() != 1 ) .

Long answer

&& is the AND operator r.getRow() != 1 compares the row of the edited cell with the row number that you want to be skipped.

Full code minimal modification

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange("E" + row.toString()).setValue(time);
  };
};

Full code improved

Taking advantage that onEdit() event gets an event object that include the range.

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    s.getRange("E" + row.toString()).setValue(time);
  };
};

References

Google-sheets – Update the reference in Indirect() when new columns are added

You should mark column AG in some way so it can be identified as the column that indirect needs. For example, by placing "Data" in the first row of that column.

Then you can use match to find the number of that column, which becomes a part of cell reference in R1C1 notation.

=indirect($A$1 & " Data!R3C" & match("Data", 1:1, 0), false)

This formula refers the cell in 3rd row of the column with header "Data", on the sheet specified in cell A1.