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

llravlin

New Member
Joined
Sep 26, 2012
Messages
15
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
In which column is the ID number in each sheet?
 
Last edited:
Upvote 0
Paste this code into your "Main" worksheet.

Code:
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
 
Last edited:
Upvote 0
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.
Code:
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
 
Upvote 0
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):
Code:
=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...
 
Upvote 0
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?
 
Last edited:
Upvote 0
Whoops, didn't see that you posted the columns, use this instead and copy down:
Code:
=HYPERLINK("[Workbook Name.xlsx]data!B"&MATCH(D1,'data'!B:B,0), D1)
 
Upvote 0
And in addition to mumps solution, just add ScrollRow:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,203,069
Messages
6,053,352
Members
444,655
Latest member
didr

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