Hyperlink to other worksheet using hyperlink function in excel

Question

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

Answer 1

=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.

Answer 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

Answer 3

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

Answer 4

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