Hyperlink same sheet "cannot open specified file" error

[exsalad]

This HAS to be a simple fix.... Could not find a string with this solution in it yet.

Trying to set up a hyperlink to reference another location in the same sheet.
Used the example in Excel as a model to set this up so placed this formula in cell B1:
=hyperlink([Book1]Sheet1!a1,"LINK")
this defaults to =hyperlink(Sheet1!a1,"LINK") since Book1 is the current wb.
clicking on "link" in cell B1 produces "Cannot open specified file" error.
I need the hyperlink to be dynamic, so I can't use the CTRL+K hyperlink function.

Thanks in advance for any help

 

[exsalad]

Thanks Andrew, that works perfectly.
Now that I have the sheet set up, I would like to adjust the view area so that when a hyperlink is selected, the screen updates so that the target cell is in the top left corner. This works when using the following formula with a CTRL+K hyperlink.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)<o></o></pre>
Application.Goto ActiveCell, True<o></o></pre>
End Sub

This does not work, however, with the formula. Any suggestions?

<o></o></pre>

 

[Andrew Poulsom]

Sorry, but the FollowHyperlink event doesn't fire from a cell containing a HYPERLINK formula. I don't know a solution.

 

[excel_solutions]

Sure:

=HYPERLINK(MID(CELL("FileName",$A$1),FIND("[",CELL("FileName",$A$1)),255)&"!"&CELL("address",A1))

Cannot open the specified file:

=IFERROR(HYPERLINK(INDEX($B5:$C5,MATCH($H$2,$B5,0),2)),HYPERLINK(INDEX($B5:$C5,MATCH($AM$2,$B5,0),2)))