Dynamic Hyperlink - Type a region name into a cell which drives a hyperlink

[Richynero]

Hello everyone, I have a spreadsheet with about 80+ sheets. I want to hyperlink to them.

I have a contents page at the front which is fine. But within the spreadsheet I want to create a dynamic hyperlink.

I have sheets named Region 1, Region 2 up to region 40. I want to be able to type Region 4 in cell A2 that drives a hyperlink in cell A1. I don't want to use VBA, I can do this using VBA but would like a non-vba solution. I am sure this can be done but I am struggling with the syntax using the HYPERLINK().

Thanking you all in advance.

Richard

 

[Weaver]

I really hope there's an easier way than this, but it appears to work

=HYPERLINK(MID(LEFT(CELL("filename"),FIND("]",CELL("filename"))),FIND("[",CELL("filename")),255)&"'region "&A1&"'!A1","Link")

If you just type the sheet number (the 'region ' bit is included in the formula) it ought to work.

The cell reference in red shows what you'd need to change if you want the hyperlink to go to a cell other than A1.

 

[Richynero]

That is sooo weird. This is the formula I was just playing with before I read your post:

=MID(CELL("filename"),FIND("[",CELL("filename")),(FIND("]",CELL("filename"))+1)-FIND("[",CELL("filename")))

But couldnt get this to work inside the hyperlink(). Going to try your formula and let you know if I can get it to work.

Thanks alot, been stuck on this for a while now.

 

[Richynero]

Weaver!

It worked perfectly, now I understand the syntax I should be able to use this in many more of my enterprise models.

I will probably move the complex mid to another cell and then just reference it as I think CELL() is volatile, which could slow down my 100 sheet monster down a little.

Thanks for solving my problem and thank you for responding so quickly.

 

[Richynero]

A simpler method which provided by Excel Wizard Andy Pope.

With the sheet name contained in cell A2.

=HYPERLINK("#'" & A2 & "'!A1",A2)

 

[Weaver]

a simpler method which provided by excel wizard andy pope.

With the sheet name contained in cell a2.

=hyperlink("#'" & a2 & "'!a1",a2)

LOL - happy to hear it. Figured it would have to be something along a completely different tack. Was there any explanation of what the # represents and in what other circumstances it can be used?

You'd think that if the workbook name was omitted, it would just assume it was the current one anyway.

 

[Richynero]

Explanation from an Internet friend..

"Yes, without it the HYPERLINK function will not work correctly :P

In the help the example for linking to a cell on a different sheet within the same workbook is,
=HYPERLINK("[Budget.xlsx]September!E56", E56)

The # can be used instead of the [Budget.xlsx] part that defines the workbook name. I guess it's shorthand for relative to this workbook."