Thread: [Solved] Pivot Table Drill Down

I hope I can explain this adequately, so here goes.

I have a large table with a column for Makes, and one for Models with each make having multiple models, but each model having only one make. One to may relationship. What I would like to do on my pivot table is have the report filter for the Make then only show the Models that have that make and none other.

Any ideas?

TIA

Hi Sdgenxr,

Perhaps I'm not following you, but that's exactly how a pivot table works.

Drag the "Make" field to the Row Labels area, then the "Models" field to the Row Labels area, then some value field to the Values area.

Click the filter button in the cell labeled 'Row Labels' and un-check 'Select All'. Then just select the Make you want to filter by. Once you do that, only the corresponding models will show up as sub-row headers.

If that's not the issue, try posting a workbook with your current layout/data, and what you want to/expect to see.

The default Page Filter for Model won't dynamically alter based on the selection made for Make...

An alternative ...

Append your table at source so as to create a secondary Model field which only lists those Models applicable to the Make Page Filter selection on the PT, eg:

Sheet1:
E1: Models
E2: =REPT($B2,OR(Sheet4!$B$1="(All)",Sheet4!$B$1=$A2))
(given this is a table the remaining rows should populate automatically with the formula)

Make MODELS the 2nd Page Filter (rather than model)

In PT Options on the Data Tab set "Retain items deleted from the data source" to None.

Now add some VBA to your file, right click on Sheet4 tab and select View Code, insert the below into resulting window:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.EnableEvents = False
Target.PivotCache.Refresh
Application.EnableEvents = True
End Sub

Now you should find that if you alter Make the Models options update dynamically to list only those models relating the Model chosen.