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

tiltz

New Member
Joined
Apr 3, 2012
Messages
22
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.

Year Audit NameLocationAudit TypeIssues
2011 2011 Construction AssessmentUS/CANAssessmentIssues

<tbody>
</tbody>
Sheet 2 (Issues). Identifier is in column L

Issue Risk Management OwnerIssue IdIssue CategoryKey SOX Issue?Issue Local PriorityIssue Global PriorityIssue Report DateIssue Plan DateDays OverdueIssue NameIssue DescriptionAudit Name

<tbody>
</tbody>
Is there a quick VBA somebody could share to help do what I am trying to do?
 
Last edited:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

New Member
Joined
Apr 3, 2012
Messages
22
Thanks. Given sheet formats above, what would that VBA look like?
 

tiltz

New Member
Joined
Apr 3, 2012
Messages
22
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,336
Messages
5,486,246
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top