Hyperlinking from data in one sheet to matching data in another sheet
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 |
Hyperlinking from data in one sheet to matching data in another sheet
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" 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 |
Hyperlinking from data in one sheet to matching data in another sheet
Wow, that works unbelievably outstanding! Thank you very much!
Frank On Sun, 16 Dec 2007 18:38:38 -0800, "Jim Cone" wrote: 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" 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 |
Hyperlinking from data in one sheet to matching data in another sheet
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 On Sun, 16 Dec 2007 18:38:38 -0800, "Jim Cone" wrote: 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" 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 |
Hyperlinking from data in one sheet to matching data in another sheet
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 On Sun, 16 Dec 2007 23:31:03 -0500, Phrank wrote: Wow, that works unbelievably outstanding! Thank you very much! Frank On Sun, 16 Dec 2007 18:38:38 -0800, "Jim Cone" wrote: 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" 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 |
Hyperlinking from data in one sheet to matching data in another sheet
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 On Sun, 16 Dec 2007 23:31:03 -0500, Phrank wrote: Wow, that works unbelievably outstanding! Thank you very much! Frank On Sun, 16 Dec 2007 18:38:38 -0800, "Jim Cone" wrote: 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" 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 |
Hyperlinking from data in one sheet to matching data in another sheet
Thanks Jim. I'll try that.
On Mon, 17 Dec 2007 20:01:24 -0800, "Jim Cone" wrote: 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. |
All times are GMT +1. The time now is 04:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com