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?