hyperlinks in a spreadsheet

shammy

Board Regular
Joined
Oct 13, 2005
Messages
123
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi
could you post an example of your data?
Thx
 
Upvote 0
Instead of using a hyperlink, create a button in J18 and assign this macro to it:

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:


Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top