Google Sheet Static Sheet Name Reference

Question

I have a spreadsheet that contains four different sheets

Day1 | Day2 | Day3 | Summary

in the summary sheet i will be referencing a cell in either Day1,Day2,Day3 sheets

for example i'm using this formula in the summary sheet to get a value from Day3 sheet

='Day3'!$D2

so far its working however i want the sheet name (in this case the 'Day3' in the formula above) to be static.

meaning if i rename the Day3 sheet into something else for example rename it to Day4, and create another sheet named Day3,

i want the formula above to still reference ='Day3'!$D2

because as of now if i do the steps i mentioned above, ='Day3'!$D2 automatically changes to ='Day4'!$D2 when i rename the Day3 sheet

is this possible in google sheets?

Carlos M Carlos M · Accepted Answer · 2021-03-10T15:48:46

This is an example of recalculation in Google Sheets, and for your use case, there is currently no way of disabling this feature.

As a workaround, you can use Google Apps Script to create an installable trigger to change the sheet reference manually everytime the sheet structure changes.

Sample Code:

function createTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("retainReference")
    .forSpreadsheet(ss)
    .onChange()
    .create();
}

function retainReference() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Summary");
  var range = sheet.getRange('A1');
  range.setFormula("='Day3'!D2");
}

Note: You only need to run createTrigger() function manually.

Sample Output:

When renaming sheet Day3 to sheet Day4:

Reference:

Installable Triggers

Google Sheet Static Sheet Name Reference

1 Answers