+ Reply to Thread
Results 1 to 4 of 4

Dynamic Pivot drop down field

  1. #1
    Registered User
    Join Date
    01-11-2006
    Posts
    82

    Dynamic Pivot drop down field

    Hi,

    I am trying to build a dynamic pivot table and need your help.

    I have a table with data and another sheet with 4-5 groupbox with 3 or 4 option buttons in each. Based on the combination of option buttons selected, I need to get a subset of the main data table. If there is a way to dynamically select dropdown fields in a pivot, this should be achievable.

    Attached is an example which shows data and what is expected in resultant table. Even if Pivot is not possible any other method should do good. Since our boss does not like macro's she resisted an idea utilizing macros. Request if someone can put some light on this query using only formulas and radio buttons (without macros).

    Thanks,
    Praveen
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Lightbulb Re: Dynamic Pivot drop down field

    Hello Praveen
    I'm not sure you can dynamically change the filter values of a Pivot table from another sheet without using macros (could be wrong!), but by way of a possible solution using formulas, take a look at the attached reply. It does use a helper column on your Data sheet but as shown that can be hidden and I've converted you data into an Excel table to make the formula references dynamic if adding new rows of data. It could be done without the helper column using more complicated formulas or arrays but this seems simpler if it's something you can work with.

    Hope this helps.
    DBY
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-11-2006
    Posts
    82

    Re: Dynamic Pivot drop down field

    Thanks a lot DBY. This is really a fantastic solution and really helps.

    Solution is perfect for my problem. One slight problem is that when I select "both" or "all", it should ideally display everything in the datatable for that column, but it does'nt seem to do so. Any idea about that?

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Dynamic Pivot drop down field

    Glad it is of some use. Yes, I should have allowed for the selection of 'All' and 'Both', brain's not working! Take a look at the amended attachment, does this work correctly?

    DBY
    Attached Files Attached Files

+ 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