Forum Discussion
Updating Sheet Name References after Changing Sheet Name
I do understand that when using sheet name references under normal circumstances, the formula does re-write itself if you change the sheet names. But you can see here that after I change the names to Feb, if I use the INDIRECT function to call the sheet references, the formula doesn't automatically change.
I've found a work around using find and replace, it should be all good now. Though if you have a simple solution that doesn't need find and replace, I'd be open to hearing it.
Hi Tony, I don't understand the problem at all. When the table names are changed, the existing references to these worksheets are also dynamically rewritten. You don't have to do anything yourself.
If I have misunderstood your problem, please write again.
I do understand that when using sheet name references under normal circumstances, the formula does re-write itself if you change the sheet names. But you can see here that after I change the names to Feb, if I use the INDIRECT function to call the sheet references, the formula doesn't automatically change.
I've found a work around using find and replace, it should be all good now. Though if you have a simple solution that doesn't need find and replace, I'd be open to hearing it.
The information that you use INDIRECT() slipped through.
Question: You write that the information for the summary is always in other cells. That makes it all the more incomprehensible that you use INDIRECT(). But maybe there is a reason I don't know.
Instead of search and replace, you could of course write the part with the sheet name in your formula in a cell to access it dynamically. That way you only have to change it in one place.I used INDIRECT because if I wanted to do something like '1-Jan-2022'!A1 (excuse me if the syntax isn't 100% correct). It would automatically update when I changed the sheet names. But I would need to manually write the formula for Jan 2, Jan 3, Jan 4 etc. I would stick with the '1-Jan-2022'!A1 if there was a fast way to do it for all of the dates for January.