Google sheets resetting reference after deleting and replacing a sheet

Question

I have 2 sheets one and two. one references two. I need to delete the entire sheet two and replace it with another one that is similar. After I do that, one can not from the reference anymore even though sheet two is now there.

The only fix that I have found so far is to go to that cell and press enter on it again. Refreshing it doesn't seem to work.

Is there any way to get google sheets to automatically find the reference without having to go through and press enter on everything?

Answer 1

that's just not possible in a scale you imagine it. best you can do is to use =INDIRECT() when you reference between sheets. this way you can have a cell in sheet-one with the name of the sheet-two and when you want to replace sheet-two with sheet-two-new then you just re-write that sheet name in that cell and do it in this way:

• prepare whole sheet-two-new to be ready to replace sheet two
• rewrite cell for the reference
• delete sheet-two
• rename sheet-two-new to sheet-two