Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
1  
Are 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 Jul 15 '10 at 10:36

4 Answers 4

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
share|improve this answer
    
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 Jul 15 '10 at 10:45
    
@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 Jul 15 '10 at 11:17
    
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 Jul 15 '10 at 16:07
    
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 Jul 15 '10 at 16:15
    
Got it...! Here's the solution: =HYPERLINK("[Verlofkaarten.xlsm]" & A2 & "!A1";"Name") –  Martijn Jul 15 '10 at 16:40
=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.

share|improve this answer

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")

share|improve this answer
    
I'm not at all clear why "col H (starting at row 2 in this case)" when question refers to A2, why copy down when only a single link has been requested, and your formula (at least without some explanation) at first glance looks much more complex than necessary. –  pnuts Oct 19 '12 at 23:53

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"))

".\" just means "this file" .

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.