Maintain Formula References when replacing tabs

[maddogp]

Hi there!

I have a spreadsheet that has three tabs: "production," "targets," and "status."

Currently, the entire spreadsheet is manual, and all the data on all three tabs is inputted by hand. I am updating it so that much of the data in "targets" and "status" is automatically populated based on "production."

The problem is that the entire contents of the "production" tab gets changed once a week. The whole tab is copied to another, local spreadsheet and then edited and changed by hand. The new version is then copied into the main spreadsheet, the old "production" tab is deleted, and the new tab is then renamed "production."

All of my formulas in "targets" and "status" reference the name of "production"- but when the old tab is deleted, the references are lost, even though the new tab is immediately renamed "production." Is there a way to write the formulas so that they always just search for the name "production" - display an error when there is no such tab, but find the new tab when it is named as such?

[Jonmo1]

Instead of deleting the original, and renaming a new one to the original name...

After you're done editing and changing the new sheet by hand..
Copy the Contents of the NEW sheet (Press CTRL + A, then CTRL + C)
Then PASTE onto A1 of the original sheet.

[maddogp]

I'm so dumb. CTRL + A doesn't work this way, but if you CTRL + A + A again it copies ALL the cells on tab.

Such an easy fix, making me feel like a dummy! Thanks!

[Jonmo1]

You're welcome.