Change filter criteria based on cell clicked

[Carpie]

Hello world.

It's my first time posting to these forums but I know you guys (and gals) can help me with anything Excel. As the title states, my current challenge is finding a way to "drill down" into data based on the cells selected. What I have to work with is an export of a Bill of Material and to keep things simple, let's say I have four fields (columns) of data: 1) Parent Item, 2) Component, 3) Due Date, 4) Promise Date.

Each parent item has roughly 3-5 "child" components to it and even those "child" components have 3-5 "child" components associated with them and so forth. These relationships can go down 10 levels. All lines have a due date and promise date associated with them.

I would like the audience to be able to see the due dates and promise dates at varying levels. So if you are looking at the highest level and you see that the promise date is outside the due date, you can then drill down to the next level and see where we are off schedule. And then drill down again, and so forth, until you are able to identify the root activity that is off schedule.

Manually how I am doing this is to put a filter on the Parent Item field and starting the filter at the top level. It will show the three child levels. If I want to drill down into one of those child levels, I copy the entry from the child field, and then go back to the parent item filter and filter on that instead. How can I do this automatically and/or with a pivot table?

 

[Luke M]

A "fast" way to do it might be to write a macro that simply takes the selected cell's text and puts that in the AutoFilter. You could then assign as shortcut key (say, Crtl+q) to call the macro.
Or, we could trigger it to occur when you double click on a cell. Would either of those work for you?

 

[Carpie]

Double clicking would be great.

I also figured that we could just use undo "to go back up a level". Essentially it would retrace our steps for us.

 

[Luke M]

Okay, let's try this. Right-click on the sheet tab, view code. Paste this in:

'Store this variable outside of the procedure so that our code remembers it
Dim parentCode As String
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Activates only when we double click in col B or A
If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    
Dim compName As String
 
compName = Target.Value
 
'Assuming headers are in row 1
If compName = "" Or Target.Row = 1 Then Exit Sub
 
If Me.FilterMode Then
    Me.ShowAllData
End If
If Target.Column = 1 Then
    'Drill back up
    Range("1:1").AutoFilter Field:=1, Criteria1:=parentCode
Else
    'Store this for late
    parentCode = Cells(Target.Row, 1).Value
    Range("1:1").AutoFilter Field:=1, Criteria1:=compName
End If
 
'Set this to true if you don't want to edit the cell after a double-click
Cancel = True
End Sub