+ Reply to Thread
Results 1 to 9 of 9

Linking Pivot table filter criteria

  1. #1
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Linking Pivot table filter criteria

    Hi All, I have 2 pivot tables (PivotTable1 and PivotTable2) which both source from the same pool of date.

    The difference between the two are that I have a different report filter criteria.

    For one the heading is "day", for the other it is "night". Each colum of data has the same entry range (names of staff) and I want to be able to link the two tables so for instance -

    If i select "Paul" from the (day) report filter on PivotTable1, I want the (night) report filter on PivotTable2 to also change to "Paul".

    Is this possible?
    Last edited by cmb80; 01-17-2010 at 02:14 PM.

  2. #2
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Re: Linking Pivot table filter criteria

    Anyone?? :-(

  3. #3
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Linking Pivot table filter criteria

    Sure, but it requires VBA. Don't know if that meets your requirement.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  4. #4
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Re: Linking Pivot table filter criteria

    Using VBA is fine.

    Do you have some code?

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

    Re: Linking Pivot table filter criteria

    If you search the Forum you will find countless threads related to controlling multiple pivots simultaneously - there are (IMO) differing layers of complexity pending version and required actions...

    Here at least the requirements "seem" straightforward enough... ie a single page/report filter with a 1 / All type approach where data source is identical, on which basis perhaps something like:

    Please Login or Register  to view this content.
    it's not clear to me if the page filter is sourced from the same field or not ?
    if so, simply change that above in red to reflect the required field name... if not you should I think be able to adapt the above as required by adjust strField as appropriate

    Note: the above also assumes PTs are located on the same sheet... unspecified

    (the above code would reside in the Sheet Object on which the PTs exist - right click sheet name in XL - View Code - paste above into resulting window)

  6. #6
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Linking Pivot table filter criteria

    Good one DO,

    Lot of dependencies (requisites), but I like the small code. Thanks for that.

  7. #7
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Re: Linking Pivot table filter criteria

    Quote Originally Posted by DonkeyOte View Post
    If you search the Forum you will find countless threads related to controlling multiple pivots simultaneously - there are (IMO) differing layers of complexity pending version and required actions...

    Here at least the requirements "seem" straightforward enough... ie a single page/report filter with a 1 / All type approach where data source is identical, on which basis perhaps something like:

    Please Login or Register  to view this content.
    it's not clear to me if the page filter is sourced from the same field or not ?
    if so, simply change that above in red to reflect the required field name... if not you should I think be able to adapt the above as required by adjust strField as appropriate

    Note: the above also assumes PTs are located on the same sheet... unspecified

    (the above code would reside in the Sheet Object on which the PTs exist - right click sheet name in XL - View Code - paste above into resulting window)
    Thanks for the response.

    I should clear up, the pivot filters are NOT sourced from the same column header - but both pivot tables source from the same data set.

    I've tried the code as you had it (by changing the red text to either of the two pivot filters) but this doesn;t seem to work. Can you explain a bit more what you mean by adapting the code with adjust str field? I'm not that au fait with VB i'm afraid

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

    Re: Linking Pivot table filter criteria

    OK, so it seems PivotTable1 uses "day" field (?) and PivotTable2 uses "night" field, correct ?

    If so, we adapt such that pending Target the strField is adjusted accordingly such that if PivotTable1 is altered then we set strField to be "day" and vice versa, so perhaps try:

    Please Login or Register  to view this content.
    above is obviously untested...

    If the fields do not contain the same names then obviously the above is open to error... if they do I guess we would ask why you're using different fields in the first instance...
    Last edited by DonkeyOte; 01-17-2010 at 02:15 PM. Reason: reworded

  9. #9
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Re: Linking Pivot table filter criteria

    Excellent, works very well thanks.

+ 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