Results 1 to 10 of 10

Thread: Pivots Table filter

  1. #1

    Pivots Table filter



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I have 3 sheets in my excel worksheet.

    1. Org
    2. DataSource
    3. Pivots Table

    My Pivot table will get the data from the DataSource sheet. I will like to have the filter of the Pivot Table from one of the cell in Org Sheet.

    How can I do that?

    Thanks.

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    If you have Excel 2010 or later, then instead of using Data Validation in a cell and hooking that cell up to some PivotTables via macros, you can do this non-macro simple alternative:1. Create a new Pivottable, and drag the field you want to filter the other PivotTables by into the PageField. So now you have a PivotTable masquerading as Data Validation across two cells, and looks like this: Country (All)2. Connect that PivotTable to the other PivotTables with a Slicer.That's it...job done.

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    I tweaked Jeff's code a 'little' bit.
    I wasn't allowed to comment in DDoE.
    Attached Files Attached Files
    Last edited by snb; 2014-08-16 at 05:26 PM.

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    THat's more consice, snb. Wrote my code at midnight, and didn't spend enough time shortening it. Note that CommandBar(51) isn't the correct commandbar, though. So the AddSlicer command isn't available from the right click PivotTables menu.

  6. #6
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    In Excel 2010 the correct indexnumber is 51
    Maybe you use 2013 ? Please check it's indexnumber.

    Code:
    MsgBox Application.CommandBars("PivotTable Context Menu").Index

  7. #7
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    @Jeef

    In the attachment you'll find an overview of the commandbar indices in Excel 2010.

    If you run the macro the Excel 2013 indices will be added in column C
    Attached Files Attached Files

  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Thanks snb. Any chance you can extract the CommandBar Names in whatever non-English versions of Excel you have at your disposal? I'm interested to see if the names change much, if at all.

  9. #9
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    I consulted my dear Dutch colleagues:

    - Commandbar names only appear in (US) English
    - the Indexnumbers appear to be different for every user, independent of the Office version.

    So you were quite right to rely on the commandbar names exclusively

  10. #10

    Max and Min

    Hello every body

    I have a problem in my excel project of my university,please help me
    I attached the excel file

    We have 4 columns and 2 sheets
    Date , Hour , Rate 1 , Rate 2
    My important date is D and E columns.And I want to calculate Max of each day and also Min of each day and the result automatically write in Sheet 2 when I write the date in sheet 2
    Now I want to calculated automatically Max and Min of each day when I write the date in sheet 2


    Please help me to solve this problem
    Thanks a lot

    Be HappyClick image for larger version. 

Name:	Pic Max Toll.png 
Views:	2 
Size:	26.7 KB 
ID:	2657EJ-Project1.xls

Posting Permissions

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