hyperlinking between two data tables sheets

[MikeG]

I have two tables with different data about employees, and I'd like to set it up so that they are hyperlinked so that if you clcik on the enployee number in one of the sheets it takes you to the same employee on the other sheet.


There are too many employees to atempt adding manual hyperlinks, does anyone have an aproach using excel macros or tools which would set this up with limited effort?


Ideally it would need to work in excel 2003 as thats what I have to work with for this project.


Any help apreciated

 

[Luke M]

This might work for you. Just need to edit the lines specified to match your needs.

[pre]

Sub CreateLinks()
Dim List1 As Range
Dim List2 As Range
Dim ws1 As String
Dim ws2 As String
Dim xName As String
Dim c1 As Range
Dim c2 As Range

'CHANGE THESE LINES AS NEEDED
ws1 = "Sheet1"
ws2 = "Sheet2"
Set List1 = Worksheets(ws1).Range("B2:B10")
Set List2 = Worksheets(ws2).Range("A2:A10")

For Each c1 In List1
xName = c1.Value
For Each c2 In List2
If c2.Value = xName And c1 <> "" And c2 <> "" Then
Worksheets(ws2).Hyperlinks.Add anchor:=c2, Address:="", _
SubAddress:="'" & ws1 & "'!" & c1.Address, TextToDisplay:=c2.Value
Worksheets(ws1).Hyperlinks.Add anchor:=c1, Address:="", _
SubAddress:="'" & ws2 & "'!" & c2.Address, TextToDisplay:=c1.Value
Exit For
End If
Next c2
Next c1

End Sub

[/pre]

 

[MikeG]

Thats just what i wanted - thanks for the help.


At some point I would like to try and change this into a macro with prompts for the ranges

so it can be used fairly universally for different ranges.


Mike