hyperlinks in a spreadsheet

[shammy]

Ok, not sure if this is possible, but it seems that nearly everything IS possible in excel.

I have a list of say 900 names (30 team names with 30 players on each team). I have a table to the right on the list with each team name hyperlinked, but what I would like is when I click the link I want it to search for the 1st cell in Column 'B' that has the same name as the link.
For example, in the list at cell B600, the cell says 'shammy'. I click on the link to the right on the list in cell J18 and what I want is for the spreadsheet find that first cell with 'shammy' in it in Column B (B600).
Hope this makes sense.
Thanks.

 

[arthurbr]

Hi
could you post an example of your data?
Thx

 

[Barry Katcher]

Instead of using a hyperlink, create a button in J18 and assign this macro to it:

Sub Macro1()
    Columns("B:B").Select
    Selection.Find(What:="shammy", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
End Sub

Repeat for the other 29 teams, changing "shammy" to whatever.

 

[shammy]

The thing is that sometimes the team name changes and the list gets changed around, so at one time 'shammy' may be at cell B600 and another time it might be at B120.

 

[shammy]

Hi
could you post an example of your data?
Thx

basically its like this:

Column A - Column B
russ walsh - Alpha
jeffwalsh - Alpha
jim walsh - Foxtrot
Joan Walsh - Foxtrot
Kris Walker - Shammy
Cory Mann - Shammy

There are say 900 names under Column A with each having a team name (Column B). There are 30 team names, and 30 players per team.

 

[shammy]

Bump

 

[shammy]

I guess this can't be done?

 

[Tom Urtis]

If your worksheet is set up as you say, with the hyperlinks such that their display text in the cell (in column J or wherever) is the same as the names in column B, then right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Personally I would not use hyperlinks in this case, since as you say the location of those names in column B changes. I would use maybe a double-click event instead, with raw text in column J instead of hyperlinks. But as long as you already have the hyperlinks established, and are maybe using them for some other additional reason, this works for me:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim hypName$, myFind As Variant
hypName = Target.TextToDisplay
Set myFind = Columns(2).Find(What:=hypName, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
If myFind Is Nothing Then
MsgBox "''" & hypName & "'' was not found in column B.", 48, "No such animal."
Exit Sub
Else
Cells(myFind.Row, 2).Activate
End If
End Sub

 

[shammy]

Thanks Tom,
works perfect.