10
2
I'm having problems using the Hyperlink function of Excel. I have a name in cell A1, In cell a2 I want a hyperlink that links to the worksheet that is named the same as the name in cell A1
Martijn
Posted 2010-07-15T09:50:45.247
Reputation: 101
18
=HYPERLINK("#'linked sheet name'!linked cell number","your message")
For example
=HYPERLINK("#'Page 2'!A4","TEST")
The linked sheet name is Page 2 and linked cell number is A4 and message is TEST. The # is shorthand for the local workbook.
www
Posted 2010-07-15T09:50:45.247
Reputation: 181
2
The HYPERLINK function is used to make a link to another sheet link this:
=HYPERLINK("[File]SheetName!A1", "NiceName" )
Since the first part is a string, if your value for SheetName is stored in cell A1 you could use CONCATENATE to build that string like this (line breaks added inside the CONCATENATE to hopefully add some clarity)
=HYPERLINK( CONCATENATE("[",
MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1),
"]",
A1 ,
"!B1" ) , "Name" )
This is quite long and painful, sorry, so someone might have a better suggestion - but I think this will work. Note that this will only work on saved files as it requires a filename to work on.
Use B1 for the cell or named ranged to link to (I guess just use A1 if you just want to open that sheet and note bothered about a specific point within it).
And "NiceName" is what appears in the cell to the user.
As way of a brief explanation, what the CONCATENATE is doing, is first extracting the filename from CELL("filename"), wrapping it in the required [], appending the sheet name (taken from cell A1), and finally appending ! and a cell name to complete the link. The result, for example, is something like the following, which should work as a target for HYPERLINK.
[FileName.xls]SheetName!A1
DMA57361
Posted 2010-07-15T09:50:45.247
Reputation: 17 581
Thanks for your answer. I first tried your first option to test if the basics work, but excel gives me the error that the file can't be opened. Doesn't the first option have to be combined with a full path name? I tried this before also using the "cell" function but couldn't get it to work, maybe you can help me out... – Martijn – 2010-07-15T10:45:30.950
@Martijn - I'm updating my answer with something that is more accurate. Apologies, but my first effort clearly wasn't checked well enough before I posted it. Please check the edited answer. – DMA57361 – 2010-07-15T11:17:03.837
Still can't get even the simple function to work.. Still gives a path error. Tried using "Cell" then copy pasted the value in [File].. path error. Tried writing down the UNC path, still an error.. can't understand what I'm doing wrong here.. – Martijn – 2010-07-15T16:07:05.830
Ok, got the simple one to work.. Now trying to create the string as shown beneath.. any idea why this doesn't work..? The name of the tab is in A2. =HYPERLINK("[Verlofkaarten.xlsm]'&A2'!A1";A2) – Martijn – 2010-07-15T16:15:34.200
Got it...! Here's the solution: =HYPERLINK("[Verlofkaarten.xlsm]" & A2 & "!A1";"Name") – Martijn – 2010-07-15T16:40:59.303
@Martijn You're use of "[Verlofkaarten.xlsm]'&A2'!A1" won't work - because it is a single string it doesn't evaluate the contents - in other words does not read the sheet name from A2 but looks for a sheet named &A2. That's why I used CONCATENATE (which sticks things together to produce a combined string as the result). If you're going to use & instead, I think it should be "[Verlofkaarten.xlsm]" & A2 & "!A1" - note the quotes are closed and reopened around & A2 &. – DMA57361 – 2010-07-15T16:42:12.390
@Martijn If you want to get it working - try putting ="[Verlofkaarten.xlsm]" & A2 & "!A1" in a cell of it's own - once you've got the string joining working, wrap it in the HYPERLINK call – DMA57361 – 2010-07-15T16:45:16.470
@Martijn - excellent, and you beat me to it as well. :) – DMA57361 – 2010-07-15T16:49:27.020
@DMA57361 Thanks for your help.. the brainstorming did it for me....;) – Martijn – 2010-07-16T09:25:35.280
2
Be a little cautious about using the actual Filename in the hyperlink, because you create a dependency on the existence of that Filename.
If I link to a cell in the same workbook, I use:
=HYPERLINK("[.\]Report!D4", "Click here to go to cell D4 of the Report tab")
Andrew
Posted 2010-07-15T09:50:45.247
Reputation: 21
Why specify the file at all? – Devil's Advocate – 2016-05-12T23:26:22.800
-1
This worked for me when I don't know what the name of the spreadsheet file is going to be (the users change the name for each version). My friendly text in the cell is called "LINK" because the name of the sheet needs to exist in another cell so doubling up the name confused the users. You would copy this formula down next to a list of worksheet names in col H (starting at row 2 in this case)...works out like a clickable table of contents:
=HYPERLINK( MID(CELL("filename",$A$1), FIND("[",CELL("filename",$A$1)), FIND("]",CELL("filename",$A$1))- FIND("[",CELL("filename",$A$1))+1) & "#" & $H2 & "!A1", "LINK")
Laurence Postgate
Posted 2010-07-15T09:50:45.247
Reputation: 1
1Are you using 'Click Here' as your actual hyperlink in A2? Otherwise I'm not clear on why you are showing the name in A1 and not using that as the link itself (insert hyperlink button is very useful and simple to use). – RocketGoal – 2010-07-15T10:36:41.047