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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,114
In which column is the ID number in each sheet?
 
Last edited:

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
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:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,114

ADVERTISEMENT

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
 

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
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...
 

llravlin

New Member
Joined
Sep 26, 2012
Messages
15

ADVERTISEMENT

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:

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
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)
 

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
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
 

llravlin

New Member
Joined
Sep 26, 2012
Messages
15
PERFECT!!! Thank you both mumps and BQardi, I truly appreciate it.

Happy Holidays
 

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

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
Top