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:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top