Clicking a hyperlink in Excel to set filter on a different sheet

[tiltz]

I have an Excel workbook with two sheets, basically a one-to-many setup between the two sheets. The first sheet lists projects and the second sheet lists the issues for each project. The second sheet has filters on each column
What I am attempting to do is have a user click the issues cell on the first sheet so the user is then taken to the next sheet with the filter already populated with the project selected.
Here is the format:

Sheet 1 (Audits). Identifier is under audit name. The issues column contains the hyperlink to the second tab, but I don't know how to have it filter by the audit name.

[TABLE="width: 866"]
<tbody>[TR]
[TD]Year[/TD]
[TD] Audit Name[/TD]
[TD]Location[/TD]
[TD]Audit Type[/TD]
[TD]Issues[/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD] 2011 Construction Assessment[/TD]
[TD]US/CAN[/TD]
[TD]Assessment[/TD]
[TD]Issues[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2 (Issues). Identifier is in column L

[TABLE="width: 1644"]
<tbody>[TR]
[TD]Issue Risk Management Owner[/TD]
[TD]Issue Id[/TD]
[TD]Issue Category[/TD]
[TD]Key SOX Issue?[/TD]
[TD]Issue Local Priority[/TD]
[TD]Issue Global Priority[/TD]
[TD]Issue Report Date[/TD]
[TD]Issue Plan Date[/TD]
[TD]Days Overdue[/TD]
[TD]Issue Name[/TD]
[TD]Issue Description[/TD]
[TD]Audit Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Is there a quick VBA somebody could share to help do what I am trying to do?

 

[James006]

Hello,

The quickest way is ...

Better than a hyperlink : you should use a Double-Click Event macro ...

which would feed your second sheet with the criteria required in the Filter ...

and display the filtered results ...

HTH

 

[tiltz]

Thanks. Given sheet formats above, what would that VBA look like?

 

[tiltz]

This is what I tried, but it doesn't do anything:

Private Sub Sheet1_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Cancel = True
'Update Table2
'Update Column 2
'Update Sheet2
'Change on Column 12
If ActiveCell.Column = 5 Then
Audits.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:=ActiveCell.Offset(0, -3)
'Update Sheet2
Sheet2.Activate
End If
End Sub

 

[James006]

Hello,

In the sheet 1 module (i.e Audits ) you could test :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 2 Then Exit Sub
Dim last As Long
last = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Sheet2.Range("A1:L" & last).AutoFilter
Sheet2.Range("A1:L" & last).AutoFilter Field:=12, Criteria1:=Target.Value
Cancel = True
Application.Goto Sheet2.Range("A1")
End Sub

Hope this will help

 

[tiltz]

Thank you. That worked perfectly

 

[James006]

Thank you. That worked perfectly

You are welcome ...