Create hyperlink to another row in another sheet:

[sooshil]

Hi all,

I want to create hyperlink to another row in another sheet.
I have Sheet1 and Sheet2 and they have Table1 and Table2 respectively.
Table1 has Employee column as dropdown, the list of employees being pulled from Employee column in Table2.
Table2 has all employee information like their name, address, contact etc entered as one employee per table-row.

I want to make the employee column in Table1 as hyperlink so that as we click on it, the whole table row with that employee in Table2 is selected.
Any help would be highly appreciated.

 

[Dan_W]

It's an interesting question you've posed - I've never had to think about dunamic hyperilnks like that before, so I took a look around. I suspect
this short video probably gets you close to what you're looking for?

 

[sooshil]

Wow....
Before I watch this video, I was somewhat able to code myself to achieve the result. Then I watch this video. And now I am preferring the HYPERLINK function method than VBA.
Thank you so much Dan.

 

[Dan_W]

You're more than welcome. That's great to hear that you managed to achieve the result yourself. code It's always a great feeling of accomplishment.

 

[mcgee1505]

I have a similar problem, any chance you can provide the code?

 

[sooshil]

Hi mcgee1505

Sure, I can provide the code.
I have two codes. One is the worksheet code placed in the sheet having hyperlink. As soon as I double click on that link, a procedure will be called. That will select a row on the destination sheet.

Here in this code, "DataEntry" is the name of the table and "Emp Name" is the column. A cell in that column, I will double click to select the respective row in another sheet. When I double click, I want to call a sub proceture "SelectEmpRow"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo myError
    If Not Intersect(Target, Range("DataEntry[Emp Name]")) Is Nothing Then
        Call SelectEmpRow
        Cancel = True
    End If
myError:
End Sub

The following code is placed in a module.

Sub SelectEmpRow()
    Application.ScreenUpdating = False
    On Error GoTo myError
    Dim rowNo As Integer
    Dim selected
    Dim toSelect As Range
    Dim tableStartingRow As Integer
    
    
    ShHome.Select 'ShHome is my source sheet.
    selected = Selection.Value
    ShHr.Activate 'shHr is my destination sheet.
    
    'tableStartingRow is where the table row starts in ShHr sheet.
    tableStartingRow = Application.Match("SN", Range("A:A"), 0)
    
    ' "AllData" is my table in ShHr sheet and "EmpName" is a column on that table.
    rowNo = Application.Match(selected, Range("AllData[EmpName]"), 0)
    
    ActiveSheet.Cells(rowNo + tableStartingRow, 5).Select '5 refers to the fifth column [EmpName]
    ActiveCell.EntireRow.Select
    
    Application.ScreenUpdating = True
myError:
    
End Sub

Hope, this helps.