INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...This is easily the most helpful website I've ever used, and this is the best forum with the quickest response time bar none...."
Geography
Where in the world do Tek-Tips members come from?
|
Excel Dynamic Hyperlinks (2)
|
|
|
LGMan (MIS) |
23 Jul 12 18:27 |
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
|
|
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 (MIS) |
24 Jul 12 6:15 |
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. |
|
EXAMPLES as I requested???
Hyperlinks are STATIC!!! Not appropriate for lookups in lists! Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
|
LGMan (MIS) |
24 Jul 12 10:15 |
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
CODESheet 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 CODEsheet 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 |
|
Quote (SkipVought)and expected results
based on your specific example, please. Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
|
LGMan (MIS) |
24 Jul 12 10:56 |
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 |
|
GENERALIZATIONS are not SPECIFICS!!! Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
Quote: 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 (MIS) |
24 Jul 12 11:55 |
Hi Skip as per request
CODERange("B3").Select
Selection.Copy
Sheets("Emp Address").Select
ActiveSheet.Range("$A$1:$H$22").AutoFilter Field:=6, Criteria1:="=20050011" _
, Operator:=xlAnd |
|
Paste this in your MODULE...
CODESub 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
CODEPrivate 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 (MIS) |
24 Jul 12 12:40 |
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 (MIS) |
25 Jul 12 11:54 |
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?
|
|
So now you need to ALSO specify what column ...
CODESub FilterTable(EMPID As String, COL as integer)
Sheets("Emp Address").UsedRange.AutoFilter _
Field:=COL, Criteria1:=EMPID
End Sub CODEPrivate 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, Target.column
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 (MIS) |
25 Jul 12 12:11 |
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 |
|
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 (MIS) |
25 Jul 12 18:37 |
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. |
|
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...
CODECODE
Sub FilterTable(EMPID As String, COL as integer)
'
On error resume next
With Sheets("Emp Address")
.showalldata
On error goto 0
'
.UsedRange.AutoFilter _
Field:=COL, Criteria1:=EMPID
End with
End Sub Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
|
LGMan (MIS) |
26 Jul 12 3:55 |
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 (TechnicalUser) |
26 Jul 12 4:18 |
Quote (SkipVought)Hyperlinks are STATIC!!! Not appropriate for lookups in lists!
There'a a dynamic HYPERLINK function in excel. An example:
=HYPERLINK("#'Sheet2'!A"&B1,"Jump to Sheet2 col A, row no in B1")
In case of the same sheet:
=HYPERLINK("#A"&B1,"Jump to col A, row no in B1")
and named range:
=HYPERLINK("#MyRange","Jump to named MyRange") combo |
|
|
LGMan (MIS) |
26 Jul 12 13:01 |
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 |
|
combo, I have egg on my face. 
LG, change the statement in the Worksheet_Change event
CODEFilterTable Target.Value, Cells(1, Target.Column).Value
and change the code for the filter...
CODESub FilterTable(EMPID As String, COL As String)
'
On Error Resume Next
With Sheets("Emp Address")
.ShowAllData
On Error GoTo 0
'
.UsedRange.AutoFilter _
Field:=.Rows(1).Find(COL).Column, Criteria1:=EMPID
End With
End Sub Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
|
LGMan (MIS) |
26 Jul 12 15:46 |
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 |
|
Quote:Dept_ID and EMP_ID are are on row 2
CODEField:=.Rows(2).Find(COL).Column, Criteria1:=EMPID Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
|
LGMan (MIS) |
26 Jul 12 17:30 |
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
CODEPrivate 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
CODESub 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
CODE.UsedRange.AutoFilter _
Field:=.Rows(2).Find(COL).Column, Criteria1:=EMPID |
|
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 (MIS) |
27 Jul 12 17:30 |
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 |
|
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 (MIS) |
28 Jul 12 6:17 |
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
CODEABC2 ABC211 these are validations
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 |
|
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 (MIS) |
28 Jul 12 8:50 |
Skip, thanks very much for keeping at it, I apologise as I should have spotted that.
Another deserved star for your collection! |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One