+ Reply to Thread
Results 1 to 8 of 8

Excel 2007 : [Solved] Pivot Table Drill Down

  1. #1
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    [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
    Last edited by sdgenxr; 06-11-2009 at 10:50 AM. Reason: Solved

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Pivot Table Drill Down

    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.

  3. #3
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Pivot Table Drill Down

    I would prefer to have the entire table filtered by the make and model by placing those fields in the "Report Filter" area and other fields in the values and axis fields.

    In the attachment I've selected "Canon" as the Make and would like only the five different models shown on the table to be selectable as a second Report Filter for the Pivot table.

    Thanks for the help Paul.
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot Table Drill Down

    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:

    Please Login or Register  to view this content.
    Now you should find that if you alter Make the Models options update dynamically to list only those models relating the Model chosen.

  5. #5
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Pivot Table Drill Down

    Awesome, thanks DonkeyOte! Now I just have to spend some time figuring out how this works to use it in the future.

  6. #6
    Registered User
    Join Date
    11-18-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    2

    Re: Pivot Table Drill Down

    Hi I tried the same, but the Models filter does not show any value, can you please help me with the modified sheet

  7. #7
    Registered User
    Join Date
    11-18-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    2

    Re: Pivot Table Drill Down

    To be precise it does not work on multiple selection, it works fine if we make selection on one Make, the moment we select multiple if does not work, so if you can help solving this issue

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Excel 2007 : [Solved] Pivot Table Drill Down

    vn_kothari,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1