creating a hyperlink in a cell after a different input is added in that cell

[rishijain11]

i'm curious if this is possible:

first, lets say at the bottom of my worksheet i have cells A100-A110 each containing the text of either "V1", "V2"....."V10",


now say if i enter into a blank cell somewhere in a particular column the text "V1", is there a way to create a formula where it turns that cell into a hyperlink to the respective cell at the bottom of my worksheet? e.g. clicking on the cell that has the text "V5" in it will bring me to the cell at the bottom of the worksheet that has the text "V5" in it as well...

any help is much appreciated...
thanks

[daffodil11]

This VBA code can do it. To use this code, first go into your spreadsheet, hit Alt+F11.

On the left side of the screen you'll see a little tree thingy. Double click the tab this will apply to.

Paste this code there:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GTFO

If Not Intersect(Target, Range("K3:K10")) Is Nothing Then

    Cell = Target
    
    Matchy = "Sheet1!B" & WorksheetFunction.Match(Range(Target.Address), Range("B1:B27"), 0)
     
    ActiveCell.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:= _
        Matchy, TextToDisplay:=Cell
        
End If

GTFO:
End Sub

You just need to change the ranges to accommodate your needs. In my target, I'm detecting changes made to K3:K10. Change this to where you'll be typing in V1, V2, etc.

"Sheet1!B" & WorksheetFunction.Match(Range(Target.Address), Range("B1:B27"), 0) - Change the sheet name, Column Letter, and B1:B27 for the column where you're looking for a match.


Then just close that sheet. Now when you make any changes in your target range, it will find the match in your second range and instantly convert that cell to a hyperlink that links to the place it found it.