SOLVED

Updating Sheet Name References after Changing Sheet Name

Copper Contributor

Tony_Nguyen_0-1662865377138.png

 

Tony_Nguyen_7-1662864065792.png

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:

Tony_Nguyen_9-1662865049664.png

 

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

 

 

 

 

 

8 Replies

@Tony_Nguyen 

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.

 

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 workbook

GeorgieAnne_0-1662919435165.png

And now you see the button is active

GeorgieAnne_1-1662919523670.png

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.

best response confirmed by Tony_Nguyen (Copper Contributor)
Solution

@dscheikey 

Tony_Nguyen_0-1662947800090.png

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. 

 

 

@Tony_Nguyen 

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.

@dscheikey 

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. 

 

@Tony_Nguyen 

So my opinion: If the spreadsheets already exist and you rename them, then you should do the work and reference the cells directly. I have also given you an example in the attached document of how you can implement this very quickly. You can first build the direct referencing as text. You can do this with the help of formulas. Then copy the function result as text into another cell. Then the formula does not work at first. However, you can change this for all cells at the same time by using the menu command Text from Columns. Then your formulas are ready and you can use them. After renaming the worksheets, you do not have to do anything else. The formulas will automatically re-reference themselves.

If you need the indirect referencing because the spreadsheets are not created until you have already built your formulas, you can use my second example in which only the month is still built into your indirect formula.

@Tony_Nguyen 

 

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)

 

2022-09-12_12-07-29.gif

@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!

IMG_6603.jpeg

1 best response

Accepted Solutions
best response confirmed by Tony_Nguyen (Copper Contributor)
Solution

@dscheikey 

Tony_Nguyen_0-1662947800090.png

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. 

 

 

View solution in original post