Forum Discussion
Updating Sheet Name References after Changing Sheet Name
I want to be able to change the names of all my sheets without needing to change the formulae for the sheet references
I have a macro that automatically changes the sheet names for me. Now with the current formulae for the sheet references, the formula will automatically update after I change the sheet names. So that problem is solved. However, I can't think of a way to quickly go from Jan 1 to Jan 31 with this way of doing the formulae other than manually typing it in each row.
I have an alternative way of doing the sheet references that allows it to quickly go from Jan 1 to 31, and it looks like so:
However, when I rename the sheet names to Feb, I get a reference error. So I would have to manually change the "-Jan-2022" in the formula to "-Feb-2022". Note that I can't just drag the fill handle down after changing it to "-Feb-2022" because "Test1", "Test2" etc. are in completely different positions in their respective sheets.
So what method allows me to update the sheet names without changing the formulae of the sheet references AND I can quickly increment from Jan 1 to Jan 31 for the formulae.
Thanks,
Tony
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.
8 Replies
Tony_Nguyen Using three cells to lay out the date and then referencing with IFERROR and INDIRECT formulas combined would allow you to update the month, drag down and you’d have two clicks to update your references. You could use a singular month and year reference cell to the side as well so your down to one column for the day. Screenshots attached of how I was able to make this function. Additionally, if you use data validation to create a month list you can have it as simple as a dropdown for the month. I’m sure this won’t help OP but maybe someone else will get use of this!
Hoping this will help - you should be able to include this in your formula by replacing the "1-Jan-2022".
As you can see, the formula updates even after the sheet name is revised.
Hope this helps.
Dexter
=RIGHT(CELL("filename",Test3!O5),LEN(CELL("filename",Test3!O5))-SEARCH("xlsx",CELL("filename",Test3!O5))-4)HelloTony_Nguyen, I guess this has to do with LINKS.
When you have a formula that refers to cells, and then you copy that worksheet to another place, renaming also does the same you will produce links. these links look at the original formula and the cells it point to. If you would go to the Data tab, and you will see the button activated you have links. No links are in my workbookAnd now you see the button is active
Using this Edit Links button will allow you to redirect the link to where it should be. if you have many you many want to write some VBA code.
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.
