Link from one worksheet to another worksheet based on "Employee ID"

[llravlin]

Hi,

I have a workbook with 2 sheets. Using the "Main" sheet, I want a user to click on an employee ID number and the user is taken to the "data" sheet matching that Employee ID number. I'm not sure of the best way to do this and am looking for direction on where to start.

Any help is appreciated.

 

[mumps]

In which column is the ID number in each sheet?

 

[llravlin]

COLUMN D in the "Main" sheet and COLUMN B in the "data" sheet.

 

[Krayons]

Paste this code into your "Main" worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

	'If you select more than one cell, ignore
	If Target.CountLarge > 1 Then Exit Sub

	'If you select a blank cell, ignore
	If Target.Value = vbNullString Then Exit Sub

	Dim Finder, ClickRange

	'We want the event to fire if you click in column D anywhere
	Set ClickRange = Sheets("Main").Range("D:D")

	'If you click somewhere else, ignore
	If Intersect(Target, ClickRange) Is Nothing Then Exit Sub

	'Look for the value in data sheet column B
	Set Finder = Sheets("data").Range("B:B").Find(Target.Value, LookAt:=xlWhole)

	'If we don't find it, Exit Sub
	If Finder Is Nothing Then Exit Sub

	'If we find it, select main sheet and select the cell
	Sheets("Main").Activate
	Finder.Select

End Sub

Answer found here: http://stackoverflow.com/questions/30513192/excel-vba-search-another-sheet-on-cell-click

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Main" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Click on any cell in column D.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Set foundVal = Sheets("data").Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("data").Activate
        foundVal.Select
    End If
    Application.ScreenUpdating = False
End Sub

 

[BQardi]

Or a solution without the need for VBA (replace "Workbook Name.xlsx" with the name of your workbook):
If the employee ID's are in column B in "Main" -sheet and employee ID's are in column A in "data" -sheet (replace if needed):

=HYPERLINK("[Workbook Name.xlsx]data!A"&MATCH(B1,'data'!A:A,0), B1)

This will jump to data sheet and down to employee ID of the matching hyperlink clicked...

 

[llravlin]

mumps...

This doesn't work as expected. Clicking on any value from Column D opens the "data" sheet, but not to the right value...only to the last one looked at. I don't know if it matters, but there are multiple rows with the same Employee ID...I just assumed it would open the first one found. Could that be the problem?

 

[BQardi]

Whoops, didn't see that you posted the columns, use this instead and copy down:

=HYPERLINK("[Workbook Name.xlsx]data!B"&MATCH(D1,'data'!B:B,0), D1)

 

[BQardi]

And in addition to mumps solution, just add ScrollRow:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Set foundVal = Sheets("data").Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("data").Activate
        foundVal.Select
[COLOR=#ff0000]        ActiveWindow.ScrollRow = foundVal.Row[/COLOR]
    End If
    Application.ScreenUpdating = False
End Sub

 

[llravlin]

PERFECT!!! Thank you both mumps and BQardi, I truly appreciate it.

Happy Holidays