CODEWITHSUNDEEP
google-sheetsreferencegoogle-sheets-formula
Django Unchained
Django Unchained

Reputation: 105

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

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

Upvotes: 2

Views: 2065

Answers (1)

CMB
CMB

Reputation: 5163

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);
}

Upvotes: 2

Related Questions