Help with auto sheet naming and hyperlinks

Jeron

New Member
Joined
May 1, 2017
Messages
5
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

open
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top