Maintain cell reference when deleting rows in the original table - Google Sheets/Excel

user_15316004 0
Viewed 586

I have a table that basically shows the data of another table. The problem is: I have some script procedures going in the original table that envolve deleting rows and when it happens I lose the reference to the place of my row.

How can I fix this?

Already tried this: ='INVENTORY'!A3=' »»»»»»»»» INVENTORY'!$A$3

google-sheets-formula google-sheets reference
1 Answers

When an entire row or column is deleted either manually or by script, Google Sheets replaces all the formulas that refer to it as #REF.

You can use INDIRECT to make a reference truly static: either manually, or by script as well.

Formula Sample:

=INDIRECT("Form!B7")*INDIRECT("Form!F7")'

Script Sample:

function submitData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); //Form Sheet
  var datasheet = ss.getSheetByName("Data"); //Data Sheet
   
  // get range and formula
  var dataRange = datasheet.getRange("G1");
  var formula = dataRange.getFormula();

  // delete row
  formSS.deleteRow(7);

  // redefine formula
  formula = '=INDIRECT("Form!B7")*INDIRECT("Form!F7")';
  
  // set new formula
  dataRange.setFormula(formula);
}
user_146060490
Related