Hyperlink to other worksheet using hyperlink function in excel

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

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

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