Help with auto sheet naming and hyperlinks

[Jeron]

Hey everyone. I have a workbook that I use to bid construction jobs with, that I need to update a little.

Basically, I have a main totals sheet, and multiple other sheets that I use to add up different materials and labor for different aspects of the job. Molding materials & labor, sheetrock materials and labor, paint materials and labor, etc, etc. All the sheets add up and are displayed on the main totals sheet.

On bigger jobs, it gets hard knowing what sheets are for what aspects. So I have already added something that automatically changes the sheet name based on what is typed into cell A2. That works.

I've also added hyperlinks on the main totals page that go to each sheet using "named ranges" that don't break when the sheet name is changed. That works also. Although the hyperlink text just says "page 1", "page 2", etc.

My question is can I enter "sheetrock" into cell A2, on the 3rd sheet in the workbook, and have it automatically change the name of sheet 3 to "sheetrock", and change the hyperlink text on the main total page to also say "sheetrock".

Same goes for every other sheet. If I go to sheet 7 and put "paint" in cell A2, it will change the sheet name to say "paint" and also change the hyperlink text to say "paint" on the main totals page. Also, this obviously can't break the hyperlink from the 1st sheet to the sheet it's linking to.

I've added a link to a google drive folder with screenshots, and also a copy of the excel file, it that will help anyone.

I hope all this makes sense of what I'm trying to do.

Thanks in advance

https://drive.google.com/open?id=1LJjruKLQBWBd_wAlGyj_R3PVmuHbIWpv

 

[jjasmith4]

Take a look at the HYPERLINK function. Not Ctrl-K but =HYPERLINK(link_location,friendly_name). Within that you can use formulas to refer to any sheet's cell A2 or anything else.

 

[Jeron]

That is what I used to link with originally. It has my "named range" as the link location. That makes it so the sheet can change names and the hyperlink won't break. But I'm trying to find a way for the "friendly _name" to be dynamic and change to whatever the new name is of the particular sheet, which I typed into cell A2.

Basically, I want to type something into cell A2 of any sheet and then have that same sheet name change to what was typed, and also the hyperlink text on the first sheet to change to the new sheet name.