Using a hyperlink to find information on a different worksheet
 

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

Using a hyperlink to find information on a different worksheet
 

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" wrote in message
...
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

Using a hyperlink to find information on a different worksheet
 

Hi Doug,

Very clever - worked a treat.

Mamy thanks

Tony

-----Original Message-----
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" wrote in
message
...
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



.

Using a hyperlink to find information on a different worksheet
 

Tony,

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

Doug

"Tony" wrote in message
...
Hi Doug,

Very clever - worked a treat.

Mamy thanks

Tony

-----Original Message-----
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" wrote in
message
...
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



.