Thread: look up name click on name, match & take to data sheet

This can be done with an event macro built into your misspelled Names& Data sheet.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim vFIND As Range, vFIRST As Range

On Error Resume Next
With Sheets("Names& Data")
    Select Case Target.Column
        Case 3, 8
            Set vFIND = .Range("B:B").Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not vFIND Is Nothing Then
                Do
                    If vFIND.Offset(, -1).Value = Target.Offset(, 1).Value Then
                        Cancel = True
                        .Activate
                        vFIND.EntireRow.Select
                        Exit Do
                    End If
                    Set vFIRST = vFIND
                    Set vFIND = .Range("B:B").FindNext(vFIND)
                Loop Until vFIND.Address = vFIRST.Address
            Else
                MsgBox "Not found"
                Exit Sub
            End If

        Case 4, 9
            Set vFIND = .Range("A:A").Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not vFIND Is Nothing Then
                Do
                    If vFIND.Offset(, 1).Value = Target.Offset(, -1).Value Then
                        Cancel = True
                        .Select
                        vFIND.EntireRow.Select
                        Exit Do
                    End If
                    Set vFIRST = vFIND
                    Set vFIND = .Range("A:A").FindNext(vFIND)
                Loop Until vFIND.Address = vFIRST.Address
            Else
                MsgBox "Not found"
                Exit Sub
            End If
    End Select
End With

End Sub

How/Where to install the macro:

1. Open up your workbook
2. Right-click on the sheet tab and select View Code
3. Copy and Paste in your code (given above)
4. Get out of VBA (Press Alt+Q)
5. Save as a macro-enabled workbook (*.xlsm)


Now doubleclick on any name and if it exists on the other sheet, you will be taken there.

Originally Posted by dangelor

You might try using hyperlinks to defined named ranges...

Eek! 800 separate named ranges.... I don't recommend that.