EXCEL CONVERT LIST TO HYPERLINKS & KEEP HYPERLINK EVEN WITH NAME CHANGE

[Chevon]

Hello. I am trying to make a template for my company, which has different departments, for budget tracking. I want to hyperlink the list of sheet names, but each department will name its sheets differently, so the hyperlink has to work when the tab name changes. I am not good with macros or coding, so is there an easy formula to use?

 

[saboh12617]

Hello,

You can use a mix of CELL and INDIRECT formulas like so:

=LET(celAdr,CELL("address",xyz!A1),
HYPERLINK(celAdr,RIGHT(celAdr,LEN(celAdr)-SEARCH("]",celAdr))))

EDIT: i noticed this is not valid with the names you gave to your sheets with #, it adds ['] which seems to mess up the hyperlinks.

Otherwise in VBA I see 2 possible solutions:

• Or you make an event listener on the worksheet selection to check if it has been renamed. If so, you update the hyperlink subAddress.
• Or you make the hyperlink actually run a macro, which will look for the sheet by number instead of name, and activate it. The idea is that you could add a column to your table to keep track of sheets number. An important thing however is that if your colleagues reorder the sheets, this gets messed up.

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Chevon]

Hello. I am trying to make a template for my company, which has different departments, for budget tracking. I want to hyperlink the list of sheet names, but each department will name its sheets differently, so the hyperlink has to work when the tab name changes. I am not good with macros or coding, so is there an easy formula to use?

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you. I have updated my profile.

 

[Fluff]

Thanks for that.
Do you actually have the # signs before the sheet names?

 

[Chevon]

Thanks for that.
Do you actually have the # signs before the sheet names?

I do right now, but I can change that if it makes a formula easier. I was looking for something generic, as each department will rename the sheets later.

 

[Fluff]

Actually, no need to change them, you can use

=LET(a,CELL("address",'#### Search'!H4),HYPERLINK("#"&a,TEXTAFTER(a,"]")))