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

 

[LGMan]

Thanks Combo, that interesting and one for the back pocket

Skip, any advice on what I'm missing. The solutions that you provided work as long as the Dept_ID and EMp_ID are in the same columns within both sheets, i.e if both are in Col A and B.

Many thanks for your time and expertise

 

[SkipVought]

combo, I have egg on my face.

LG, change the statement in the Worksheet_Change event

        FilterTable Target.Value, [highlight]Cells(1, Target.Column).Value[/highlight]

and change the code for the filter...

Sub FilterTable(EMPID As String, COL As [highlight]String[/highlight])
'
    On Error Resume Next
    
    With Sheets("Emp Address")
       .ShowAllData
       On Error GoTo 0
'
       .UsedRange.AutoFilter _
           Field:=[highlight].Rows(1).Find(COL).Column[/highlight], Criteria1:=EMPID
    End With
End Sub

Skip,

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

 

[LGMan]

Thanks Skip, last question on the subject, if sheet1.row1 has some data validatons which allow me to effectively filter down the company to North or South and then choose the Dept_id from a reduced list.
The employee details are then on rows 2 through to 34 with data being pulled from an array which looks at the validation selection and lists out employees accordingly.

What changes on your last piece of code to accomodate the fact that Dept_ID and EMP_ID are are on row 2

 

[SkipVought]

Dept_ID and EMP_ID are are on row [highlight]2[/highlight]

           Field:=.Rows([highlight]2[/highlight]).Find(COL).Column, Criteria1:=EMPID

Skip,

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

 

[LGMan]

Hi thought I'd try and work it out for myself and made that change before asking that last question as I ran into a run time error '1004' AutoFilter method Range class failed
here's what I have
the Worksheet Change

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, Cells(1, Target.Column).Value
        Sheets("Emp Address").Activate
    End If

Now the Module1 code

Sub FilterTable(EMPID As String, COL As String)
'
    On Error Resume Next
    
    With Sheets("Emp Address")
       .ShowAllData
       On Error GoTo 0
'
       .UsedRange.AutoFilter _
           Field:=.Rows(2).Find(COL).Column, Criteria1:=EMPID
    End With
End Sub

The run time error is here

.UsedRange.AutoFilter _
           Field:=.Rows(2).Find(COL).Column, Criteria1:=EMPID

 

[SkipVought]

Time to debug!

What are the values of COL & EMPID?

Does the value of COL appear in row 2?

Skip,

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

 

[LGMan]

Hi Skip, I've worked out that code works if a row1 is inserted into Sheet 2 (emp address). so that the headings appear on row2.
I would like the row to be inserted into Sheet1 so that my data validations can appear above the data within the array. So the array which pulls the data now starts on row 2

 

[SkipVought]

Okay, you have to realize that "change x to y" works for you because you see it both on the screen and in your head.

We are not that fortunate.

Start from scratch and explain in great detail, exactly what is on every sheet and how you want it to operate.

Skip,

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

 

[LGMan]

Hi Skip, apologies for any confusion.
This is my Summary Sheet (or sheet1) the first row in cells A1 and B1, are Data Validations. Choose within A1 from a list of ABC1, through to ABC9, With ABC2 chosen then in B1 you see a sub list of Dept_IDs which begin ABC2, and I've chosen ABC211.
Cell A2.value is Dept_ID, B2.value is EMP_ID, while C2.value is Emp_Name
All the actual Dept_IDs, EMP_IDs and Emp_Names in row 3 to 33 are gathered from an Array which references the value of cell B1

ABC2	ABC211	 [COLOR=red]these are validations[/color]
Dept_ID	EMP_ID	        Emp_Name
ABC211	20050011	B Wiggins
ABC211	20040717	M Cavendish
ABC211	20058598	C Evans
ABC211	20060702	D Smith
ABC211	20060798	D Beckham
ABC211	20060258	F Zola
ABC211	20070008	D Jones
ABC211	20050146	P Cook
ABC211	20049012	S Gerrard
ABC211	20080635	L Fignon
ABC211	20047777	B Hinault
ABC211	20053245	P Owens
ABC211	20046891	M Goss
ABC211	20055542	N Hunter
[/cocde}

What I'd like to do is be able to either click on one of the cells of Dept_IDs A3:A33 OR EMP_Ids so thats B3:B33 and be taken to the EMP_Address (or sheet2) tab and to be filtered to which value i've clicked on within those 2 ranges
Here's the Emp_Address (Sheet2) Dept_ID is in Column A, and EMP_ID is in Column F
[code]
Dept_ID	Building Name	   Addess Line 1  City/Town	Post Code  EMPID
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 Hous	   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
ABC211	Lakeland Point	   Water Drive	  Timbuktoo	TM3 3DW	   20053245
ABC212	Old School House   South Street	  Timbuktoo	TM1 7RD	   20055432
ABC211	Old School House   South Street	  Timbuktoo	TM1 7RD	   20055542
ABC211	Florida House	   Keys Drive	  Timbuktoo	TM1 2TF	   20058598
ABC211	Florida House	   Keys Drive	  Timbuktoo	TM1 2TF	   20060258
ABC211	Florida House	   Keys Drive	  Timbuktoo	TM1 2TF	   20060702
ABC211	Florida House	   Keys Drive	  Timbuktoo	TM1 2TF	   20060798
ABC212	Old School House   South Street	  Timbuktoo	TM1 7RD	   20068540
ABC211	Lakeland Point	   Water Drive	  Timbuktoo	TM1 2TF	   20070008
ABC212	Old School House   South Street   Timbuktoo	TM1 7RD	   20079812
ABC211	Lakeland Point	   Water Drive	  Timbuktoo	TM3 3DW	   20080635
ABC212	Old School House   South Street	  Timbuktoo	TM1 7RD	   20091458

 

[SkipVought]

In the Worksheet_Change event just change if target row is less than or equal to the header row then exit.

Skip,

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

 

[LGMan]

Skip, thanks very much for keeping at it, I apologise as I should have spotted that.
Another deserved star for your collection!