Click on Summary Tab to set Filter in detail Tab

[pivotguy]

I have an excel spreadsheet. When click on count values like "4" , the cursor should go to "Detail" tab and filter the Status column to "Acknowledged". So that only status =Acknowledged" records display. The same way when click on "1", only status = in progress record should display. See the attached file for better clarification.

Any suggestion.


Summary Tab
============
Status count
------ -----
Acknowledged 4
Delivered 4
In Progress 1

Detail Tab
=======
Date Status Source
---- ------ ------
1/3/2015 Acknowledged In-house
1/5/2015 Acknowledged Internal
1/6/2015 Acknowledged In-house
1/9/2015 Acknowledged In-house
1/1/2015 Delivered In-house
1/4/2015 Delivered In-house
1/7/2015 Delivered Internal
1/8/2015 Delivered External
1/2/2015 In Progress External

[Paul_Hossler]

Put this in the code sheet for 'Summary' and try it




Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub

With Worksheets("Detail")
If .AutoFilterMode Then
On Error Resume Next
.ShowAllData
On Error GoTo 0
Else
.Rows(1).AutoFilter
End If

Select Case Target.Address(False, False)
Case "A2", "B2"
.Cells.CurrentRegion.AutoFilter Field:=2, Criteria1:="Acknowledged"
Case "A3", "B3"
.Cells.CurrentRegion.AutoFilter Field:=2, Criteria1:="Delivered"
Case "A4", "B4"
.Cells.CurrentRegion.AutoFilter Field:=2, Criteria1:="In Progress"


Case "A7", "B7"
.Cells.CurrentRegion.AutoFilter Field:=3, Criteria1:="External"
Case "A8", "B8"
.Cells.CurrentRegion.AutoFilter Field:=3, Criteria1:="In-house"
Case "A9", "B9"
.Cells.CurrentRegion.AutoFilter Field:=3, Criteria1:="Internal"

Case Else
Exit Sub

End Select
.Select
End With
End Sub

[pivotguy]

Hello Paul,

You are right on target. Your code worked. Thanks a lot for your help. Excellent work.

[Paul_Hossler]

NP

There are more elegant ways, but this is probably the easiest to maintain

I thought about allowing filtering on both columns ("Acknowledged" and "Internal") but left that for another day

Can be added if you want