Excel Dynamic Hyperlinks 2

[LGMan]

Hi, I've got a workbook with 2 tabs, Sheet1 has Department_Code in Column A, Names in Col B and Employee_ID numbers in Col C. I use a data validation list in conjuction with an index formula to retrieve only the employees within the Department Code that I've chosen from the Data Validation List. The results of this typically show 15 to 25 employees dependant on thos chosen Department.
Sheet2 holds the employee address and email details for the whole company so is some 1700 rows deep. The Employee_ID number is in Col F, and I'd like to be able to create a hyperlink from the Employee_ID on Sheet1 to the same Employee_ID on Col F of Sheet2.

Hope this makes sense.

Thanks

 

[SkipVought]

Hi,

Hyperlinks are not designed for this kind of instance where a lookup formula would work just fine.

Examples of tables, given values and expected results would be appropriate in this kind of a question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Hi Skip, what kind of look up would you use?
Sheet 2 basically holds the Employee Address details across a number of cells, such as Dept_id, Building Name, City, Zip Code, Room, Cells F2:F1700 or thereabouts hold the unique Emp_Id. As an example the ("F2").Value is 20050077. I was hoping that from the Dept_Id details on sheet1 that also includes the Emp_Id, I could use a hyperlink to find 20050077 in sheet2.
the code of 20050077 is just an example, there can be up to 25 Employees listed on sheet1 for which I wanted to click on any of the 25 Emp_Ids to get to where ever they are on sheet2.

 

[SkipVought]

EXAMPLES as I requested???

Hyperlinks are STATIC!!! Not appropriate for lookups in lists!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Hi skip, I don't have any means to post my workbook so here's a sample of the data. The location of Emp_ID on sheet 2 is currently in cell F8

 Sheet 1
Dept_ID	Emp_ID	        Emp_Name
ABC211	20050011	T Wiggins
ABC211	20040717	B Cavendish
ABC211	20058598	J Evans
ABC211	20060702	D Smith
ABC211	20060798	K Beckham

sheet 2
Dept_ID	Building Name	        Addess Line 1	City/Town	Post Code	Emp_Id
ABC212	Old School House	South Street	Timbuktoo	TM1 7RD	        20030042
ABC211	Florida House	        Keys Drive	Timbuktoo	TM1 2TF	        20040717
ABC212	Old School House	South Street	Timbuktoo	TM1 7RD	        20043518
ABC211	Old School House	South Street	Timbuktoo	TM1 7RD	        20046891
ABC211	Lakeland Point	        Water Drive	Timbuktoo	TM3 3DW	        20047777
ABC211	Lakeland Point	        Water Drive	Timbuktoo	TM3 3DW	        20049012
ABC211	Florida House	        Keys Drive	Timbuktoo	TM1 2TF	        20050011
ABC211	Lakeland Point	        Water Drive	Timbuktoo	TM3 3DW	        20050146

 

[SkipVought]

and expected results

based on your specific example, please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

I want to be able to go to sheet2 and locate whichever Emp_id I click on from Sheet 1
So somewhere on sheet 2 Column F where the corresponding Emp_ID is found becomes active

 

[SkipVought]

GENERALIZATIONS are not SPECIFICS!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I wanted to click on any of the 25 Emp_Ids to get to where ever they are on sheet2

okay, try this.

turn on your AutoFilter in your table on sheet 2

Turn on your macro recorder

Select an employee id on sheet 1 and COPY

activate sheet2

select the auto filter for employee id and PASTE into the filter.

turn off your macro recorder and post back with your recorded code.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Hi Skip as per request

Range("B3").Select
    Selection.Copy
    Sheets("Emp Address").Select
    ActiveSheet.Range("$A$1:$H$22").AutoFilter Field:=6, Criteria1:="=20050011" _
        , Operator:=xlAnd

 

[SkipVought]

Paste this in your MODULE...

Sub FilterTable(EMPID As String)
    Sheets("Emp Address").UsedRange.AutoFilter _
        Field:=6, Criteria1:=EMPID
End Sub

return to your sheet and select Sheet 1.

right-click the sheet tab and select View Code
Assuming that your employee ID to select in is column B, paste this in the code window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    
    If Not Intersect(Target, Columns(2), Target.Parent.UsedRange) Is Nothing Then
        FilterTable Target.Value
        Sheets("Emp Address").Activate
    End If
End Sub

Return to sheet 1 and make an employee id selection.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Thanks Skip, I was hoping to get there without VBA, but your solution is the best way of achieving the desired result.

Many thanks for your patients

 

[LGMan]

Skip, sorry to ask and my last question on this subject, would it also be possible to do the same for the Dept_Id cells?

 

[SkipVought]

So now you need to ALSO specify what column ...

Sub FilterTable(EMPID As String[highlight], COL as integer[/highlight])
    Sheets("Emp Address").UsedRange.AutoFilter _
        Field:=[highlight]COL[/highlight], Criteria1:=EMPID
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    
    If Not Intersect(Target, Union(Columns(1),Columns(2)), Target.Parent.UsedRange) Is Nothing Then
        FilterTable Target.Value[highlight], Target.column[/highlight]
        Sheets("Emp Address").Activate
    End If
End Sub

If you want to be rigerous, then you might also change the first argument of FilterTable to something more generic, since the value could be either an EmplID or DeptID.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Thanks Skip, I was just thinking that your solution worked so well, that why not be able to click on the Dept_Ids and filter the Sheet 2 according to that and see all employees within the Dept

 

[SkipVought]

Now the situation exists that you need to consider how the filters need to work, since 2 columns get set

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Yep not quite working how I expected as the Dept_id works but the emp_id now filters out all the buildings, so shows nothing on sheet2, so I guess I've missed something.

 

[SkipVought]

That's what I'm referring to. You probably have BOTH filter assigned. Go and check. Do ALL filters need to be cleared before any one is assigned? It all depends how you want to use it.

Let me know.

To clear all filters...

CODE
Sub FilterTable(EMPID As String, COL as integer)
'[b]
    On error resume next
    
    With Sheets("Emp Address")
       .showalldata
       On error goto 0
'[/b]
       .UsedRange.AutoFilter _
           Field:=COL, Criteria1:=EMPID
    End with
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Hi Skip, yes all the filters need to be cleared. I have assigned a simple process to a button on sheet2 to clear the filters and return to sheet1, but obviously wont work if I just click on the tab to navigate.
The link on Dept_ID works, however when click on Emp_id removes all the filtering on sheet2 column B (building name) and no filtering on Emp_ID.

 

[combo]

Hyperlinks are STATIC!!! Not appropriate for lookups in lists!

There'a a dynamic HYPERLINK function in excel. An example:
[tt]=HYPERLINK("#'Sheet2'!A"&B1,"Jump to Sheet2 col A, row no in B1")[/tt]
In case of the same sheet:
[tt]=HYPERLINK("#A"&B1,"Jump to col A, row no in B1")[/tt]
and named range:
[tt]=HYPERLINK("#MyRange","Jump to named MyRange")[/tt]

combo