Hyperlinking from data in one sheet to matching data in another sheet

[Phrank]

Hi,

I've got a list of names in column A on Sheet 1 in which I've inserted
hyperlinks to associated names on Sheet 2. To have the hyperlinks
work, I've named the cells in column A on Sheet 2 to correspond with
the name (e.g., Steve is in cell A1 on Sheet1 with a hyperlink to a
cell (A10) named Steve). This works fine until I add names to Sheet2
and resort it. Obviously the hyperlink is pointing to cell A10 no
matter which name shows up in the cell after resorting, even though
it's named Steve (Bob may end up in that cell after resorting). I
need the hyperlink to pop over to the cell that Steve is in on Sheet2
no matter where it is located, and no matter how it has been sorted.
Can anyone give me an idea of what to do here? Thanks.

Frank

 

[Jim Cone]

Frank,
Link the hyperlink back to itself, then... '<<<
In the FollowHyperlink event...
Get the name from the hyperlink cell.
Select the other sheet.
Use the Match function to find the row with the name on the other sheet.
Scroll that row to the top.
Select the cell
'--
'Assumes hyperlinks are on Sheet1
'Assumes names to find are on Sheet2 in column D.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim vName
Dim vRow
vName = Me.Range(Target.SubAddress).Value
Worksheets("Sheet2").Select
vRow = Application.Match(vName, Worksheets("Sheet2").Columns("D"), 0)
ActiveWindow.ScrollRow = vRow - 1
Worksheets("Sheet2").Cells(vRow, 4).Select
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Phrank"
<[email protected]>
wrote in message
Hi,
I've got a list of names in column A on Sheet 1 in which I've inserted
hyperlinks to associated names on Sheet 2. To have the hyperlinks
work, I've named the cells in column A on Sheet 2 to correspond with
the name (e.g., Steve is in cell A1 on Sheet1 with a hyperlink to a
cell (A10) named Steve). This works fine until I add names to Sheet2
and resort it. Obviously the hyperlink is pointing to cell A10 no
matter which name shows up in the cell after resorting, even though
it's named Steve (Bob may end up in that cell after resorting). I
need the hyperlink to pop over to the cell that Steve is in on Sheet2
no matter where it is located, and no matter how it has been sorted.
Can anyone give me an idea of what to do here? Thanks.
Frank

 

[Phrank]

Wow, that works unbelievably outstanding! Thank you very much!

Frank

 

[Jim Cone]

You are welcome and thanks for the feedback.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Phrank"
wrote in message
Wow, that works unbelievably outstanding! Thank you very much!
Frank

 

[Phrank]

Hi again,

I've had an unexpected glich. It all works great if I leave Sheet1
alone, but I have sort buttons that sort the data on Sheet1 by various
columns. All of the names are in column A, and after I set up the
hyperlinks in each cell in column A to point back to itself, when I
sort the data on the sheet, the hyperlinks no longer jump to the name
in the given cell, but rather jump to the name in the original cell
where the hyperlink was setup. For example, if I have Bob in cell A10
and I set the hyperlink to point back to A10, then I sort the sheet
and Bob ends up in cell A20, the hyperlink is still pointing to the
A10, and it might be Steve that is now in A10, and the hyperlink jumps
to Steve on Sheet 2. How can I make these hyperlinks static? Thanks.

Frank

 

[Jim Cone]

I don't really know how.
Almost anything can be done In Excel with a some research and effort.
I will let you or someone else spend the time.
An alternative would be use the sheet right-click or double-click events
with almost the same code and fake the hyperlink in the cells with some
formatting.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Phrank"
wrote in message
Hi again,

I've had an unexpected glich. It all works great if I leave Sheet1
alone, but I have sort buttons that sort the data on Sheet1 by various
columns. All of the names are in column A, and after I set up the
hyperlinks in each cell in column A to point back to itself, when I
sort the data on the sheet, the hyperlinks no longer jump to the name
in the given cell, but rather jump to the name in the original cell
where the hyperlink was setup. For example, if I have Bob in cell A10
and I set the hyperlink to point back to A10, then I sort the sheet
and Bob ends up in cell A20, the hyperlink is still pointing to the
A10, and it might be Steve that is now in A10, and the hyperlink jumps
to Steve on Sheet 2. How can I make these hyperlinks static? Thanks.

Frank

 

[Phrank]

Thanks Jim. I'll try that.