Clicking a cell to filter another sheet in a specific way

[phobo]

I have 2 worksheets, one is a raw dump of info while the other is a summary similar to a pivot table.
I'd like to be able to go to the summary, click on a cell and have it take me to the other sheet and filter it in a specific way.
The original spreadsheet is quite large but an Example is below.

In this example if i clicked on:
Cell B2 (total checklists for package A) i'd like to be taken to sheet 2 and have the package column filtered to A
Cell B3 (total Completed checklists for Package A) i'd like to be taken to sheet 2 , have the package column filtered to A and the status column filtered to completed
Similar theme for the rest of the cells on sheet 1

I've tried googling this and can't seem to get any double click VBA codes working or sort out a way that doesn't involve hard coding filters for every line item, as there are many rows i am hoping to find a way that automates it on the package cell in the same row as the cell i click.

Anyone have any ideas? Also do i need to make the sheet 2 data in a table? right now i have just formatted it and put a filter in the top row.




Sheet 1 (Summary)

Package	Total Checklists (Formula is count of package column in Sheet2)	Completed (Formula is countif based on Sheet2 completed)	Remaining (Formula is Subtraction of Total and Completed)
A	3	2	1
B	1	0	1

Sheet 2 (Info Dump from software)

Package	Status
A	Completed
A	Completed
A	Outstanding
B	Outstanding

 

[mumps]

I'm not sure if I understood correctly but copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Summary" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the sheet name (in red) to suit your needs. Close the code window to return to your sheet. Double click any cell in column B.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    With Sheets("Info Dump").Cells(1, 1).CurrentRegion
        .AutoFilter 1, Target.Offset(, -1)
        .AutoFilter 2, "Completed"
    End With
End Sub

 

[phobo]

I'm not sure if I understood correctly but copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Summary" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the sheet name (in red) to suit your needs. Close the code window to return to your sheet. Double click any cell in column B.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    With Sheets("Info Dump").Cells(1, 1).CurrentRegion
        .AutoFilter 1, Target.Offset(, -1)
        .AutoFilter 2, "Completed"
    End With
End Sub

Your code works perfect! Just had to adjust autofilter placements and columns.

Just wondering how I stack it multiple times though for other columns? I can't seem to make another private sub worksheet code or just nest the ifs

2 Examples Below. Any advice on how to make this work?

Example 1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
    With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
        .AutoFilter 13, Target.Offset(, -4)
        .AutoFilter 2, "Completed"
    End With
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("J:J")) Is Nothing Then Exit Sub
    With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
        .AutoFilter 13, Target.Offset(, -3)
    End With
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
    With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
        .AutoFilter 13, Target.Offset(, -5)
        .AutoFilter 2, "<>Completed"
    End With
    
End Sub

Example 2

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
.AutoFilter 13, Target.Offset(, -4)
.AutoFilter 2, "Completed"
    End With

    If Intersect(Target, Range("J:J")) Is Nothing Then Exit Sub
With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
.AutoFilter 13, Target.Offset(, -3)
    End With

        If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
.AutoFilter 13, Target.Offset(, -5)
.AutoFilter 2, "<>Completed"
End With

End Sub

 

[mumps]

Which columns will you be double clicking? Are they B, J, K and L?

 

[phobo]

Which columns will you be double clicking? Are they B, J, K and L?

Just J, K, and L (B was relocated to one of these 3)

 

[mumps]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("J:L")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 10
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -3)
                .AutoFilter 2, "<>Completed"
            End With
        Case Is = 11
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -4)
                .AutoFilter 2, "<>Completed"
            End With
        Case Is = 10
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -5)
                .AutoFilter 2, "<>Completed"
            End With
    End Select
End Sub

 

[phobo]

Try:

Thanks! works! I found another code to clear existing filters on the other sheet so I just put that in there as well.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("J:L")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 10
            With Sheets("Checklist Dump").ShowAllData
            End With
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -3)
            End With
        Case Is = 11
            With Sheets("Checklist Dump").ShowAllData
            End With
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -4)
                .AutoFilter 2, "Completed"
            End With
        Case Is = 12
            With Sheets("Checklist Dump").ShowAllData
            End With
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -5)
                .AutoFilter 2, "<>Completed"
            End With
    End Select
End Sub

 

[mumps]

Try this shorter version:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("J:L")) Is Nothing Then Exit Sub
    Sheets("Checklist Dump").ShowAllData
    Select Case Target.Column
        Case Is = 10
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -3)
            End With
        Case Is = 11
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -4)
                .AutoFilter 2, "Completed"
            End With
        Case Is = 12
            With Sheets("Checklist Dump").Cells(1, 1).CurrentRegion
                .AutoFilter 13, Target.Offset(, -5)
                .AutoFilter 2, "<>Completed"
            End With
    End Select
End Sub