Fomrulae to Hyperlink to cell in another tab with the same value/n

[KH76]

I'm looking for a way of inserting a formula based hyperlink that when
selected would navigate to a cell in another tab (same workbook) which had
the same name. I know you can insert specific hyperkinks indivudally, but i
have thousands of rows which when clicked on need to navigate to the same
item (based on referrence id in cell) in another sheet??

 

[Pete_UK]

Do you mean that you would have a list of sheet names in a column and
you would want to click on one of them and move to a cell in that
sheet?

More details please, including the cells where the names are held.

Pete

 

[KH76]

Thanks Pete, but that's not quite what i meant.

Column A of Sheet 1 and Sheet 2 both contain a name. I want to click on the
name in sheet A and navigate to the same name in sheet 2 (without having to
apply the hyperlinks individually).

 

[Jacob Skaria]

Right click on Sheet1 tab and ViewCode> and paste the below code. Note that
the sheet is named as 'Sheet2' and you are trying from 'Sheet1'. Now get back
to workbook and *** double click *** on a cell with text...it should take you
to Sheet2 cell where the same text exists....


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim varRange As Range
Dim varFound As Variant

If Target.Count = 1 Then
If Trim(Target.Text) <> "" Then
Set varRange = Sheets("Sheet2").UsedRange
Set varFound = varRange.Find(Target.Text)
If Not varFound Is Nothing Then
Sheets("Sheet2").Activate
varFound.Select
End If
End If
End If

End Sub


If this post helps click Yes

 

[KH76]

Thanks Jacob, i've pasted the text in, but double clicking on the cell just
behaves like normal - ie. allows me to edit the text?.....

 

[Jacob Skaria]

Does the first line of code appear in red...?

The first line starting with Private and endin with Boolean) should be in
one line

OR

'you replace that with the below
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

If this post helps click Yes

 

[Pete_UK]

One way of doing it is to use a helper column in Sheet1 (eg column B),
and put this formula in B1:

=HYPERLINK("#Sheet2!R"&MATCH(A1,Sheet2!A:A,0)&"C1","jump")

Copy it down and you will see Jump next to each name. Click on the
appropriate cell and it will take you to the cell with the same name
in Sheet2.

Hope this helps.

Pete

 

[KH76]

It worked! Magic - thanks Jacob!!

Does the first line of code appear in red...?

The first line starting with Private and endin with Boolean) should be in
one line

OR

'you replace that with the below
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

If this post helps click Yes

 

[Pete_UK]

You can also put a similar formula in B1 of Sheet2:

=HYPERLINK("#Sheet1!R"&MATCH(A1,Sheet1!A:A,0)&"C1","jump")

and copy this down, so that you can jump from one sheet to the other
quite easily.

Hope this helps.

Pete