Using a hyperlink to find information on a different worksheet

[Tony]

Hi group,

I have a worksheet called League, which display a list of
Managers in my fantasy football league. I have another
worksheet called Teams, which contains the teams of the
fantasy football Managers. Each team has 11 players and
they are Grouped every 20 rows (first team begins at row 2
and 2nd team begins at rows 22 etc) and there are 85 teams
in the League.

Is it possible to create a hyerlink on the Manager name in
the League worksheet so that it automatically goes to the
Teams workskeet, finds the correct team, open the Group
and allow me to view the correct team. (Manager name
always occurs on the 1st row of a team, for example this
is first 3 rows of a sample team.)

Tony
4 Sorensen Denmark 3 0
120 G Neville England 4 0
141 Cannavaro Italy 6 0


It would be great if someone could solve this for me - it
is a popular request.

Many thanks

Tony

 

[Frank Kabel]

Hi Tony
if your file is names 'myfile.xls' try the following formula (manager
name is in column A)
=HYPERLINK("'[myfile.xls]Teams!'" &
ADDRESS(MATCH(A1,'Teams'!$A$1:$A$100,0),1))

 

[Doug Glancy]

Tony,

This will add a hyperlink to every cell in column A of League that has a
matching name in Column A of Teams. At least it works for me ...

Sub test()

Dim league_sheet, teams_sheet As Worksheet
Dim last_row_league As Long, last_row_teams As Long
Dim r As Range
Dim mgr_name As String

Set league_sheet = Worksheets("League")
Set teams_sheet = Worksheets("Teams")

last_row_league = league_sheet.Range("A" & Rows.Count).End(xlUp).Row
last_row_teams = teams_sheet.Range("A" & Rows.Count).End(xlUp).Row

For Each r In league_sheet.Range("A1:A" & last_row_league)
mgr_name = r.Value
If Not mgr_name = "" Then 'don't add hyperlinks to blank cells
On Error Resume Next
league_sheet.Hyperlinks.Add anchor:=league_sheet.Range(r.Address),
Address:="", _
SubAddress:="Teams!" & Sheet2.Range("A1:A" &
last_row_teams).Find(mgr_name).Address, _
TextToDisplay:=mgr_name
If Err <> 0 Then
r.Value = mgr_name 'if can't find a link then revert to original
value
End If
On Error GoTo 0
End If
Next r

End Sub


hth,

Doug

 

[Tony]

Hi Doug,

Very clever - worked a treat.

Mamy thanks

Tony

 

[Doug Glancy]

Tony,

I'm glad it worked for you. I learned some stuff figuring it out.

Doug