Results 1 to 6 of 6

Thread: Pivot filter

  1. #1

    Pivot filter



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

    Hi,

    I have a pivot table set up which is showing me all spend by account, Cost center, quarters and then I can drill down to line item for more detail. Works great when I want to filter for a 1,2 or 3 cost centers.

    But it fails when I want to look up multiple cost centers. My filter has over 100 unique cost centers. When I need to filter to multiple cost center, I have to go in and tick mark each cost center. sometimes I am looking for details with more than 30 cost centers. Its very annoying to filter them each time. The Cost centers I look for are in no particular range, the numbering is random.

    I am sure there is a easy way of doing it which I am not aware of. Can someone please help.

    TIA

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Unless you can use the text filters, like "contains", "begins with", "ends with", etc, then there probably isn't an easier way (unless you get into a VBA solution... to be provided by someone else).


  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    …or use slicers, these remain visible and you can just click on what you need.

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

  5. #5
    Can you not set groups of cost centres, say set 1 with a,b,c, set 2 with x,y,z etc., and then filter by group? If you used Power Pivot, I am thinking that you could create separate groups and use disconnected slicers on those groups.

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    Can you not set groups of cost centres, say set 1 with a,b,c, set 2 with x,y,z etc., and then filter by group? If you used Power Pivot, I am thinking that you could create separate groups and use disconnected slicers on those groups.
    Yes you can if a) your groups of cost centres aren't going to change and b) you can amend the source data. But this approach doesn't lend itself to ad-hoc filtering of groups. Which is certainly the case here:

    sometimes I am looking for details with more than 30 cost centers. Its very annoying to filter them each time. The Cost centers I look for are in no particular range, the numbering is random.

    Whereas my code is perfect for this. In fact I wrote it for a group of accountants who were filtering cost centres, among other things.
    Last edited by JeffreyWeir; 2014-07-29 at 12:23 PM.

Posting Permissions

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