Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formats in Pivot Tables - Excel 2007

    Hi

    I have a pivot table and if I put a conditional format over that table it works fine, as soon as I referesh the table the conditional format range changes and stops working.

    This pivot is one of many in a very large spreadsheet and all the spreadsheets have the same source data range.

    Any clues.

    Regards

    Mike

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    When you review the CF in the CF Rules Manager dialog, does the dropdown at the top say 'Show formatting rules for: This Pivot Table'?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    When you review the CF in the CF Rules Manager dialog, does the dropdown at the top say 'Show formatting rules for: This Pivot Table'?
    Hi Rory

    Yes it does.

    Another thing I have noticed is if I change anything in the pivot it effects other pivots on other tabs, eg, if I group dates into Months and Years all the other tabs change

    Regards

    Mike

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    That second bit is because they are all using the same pivot cache. If you need separate groupings you have to use separate caches.
    Can you post a sample showing the problem for the first bit? Are you using formulas to determine what to format?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    That second bit is because they are all using the same pivot cache. If you need separate groupings you have to use separate caches.
    Can you post a sample showing the problem for the first bit? Are you using formulas to determine what to format?
    Hi Rory,

    The problem is that I cannot reproduce the first issue if I create a new spreadsheet, the file I have the issue with is 30 meg and contains some sensitive data, I will see if I can strip it down.

    How can I change the cache for the appropriate pivot table without recreating each pivot, I have over 30 of them.

    Regards

    Mike

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    That second bit is because they are all using the same pivot cache. If you need separate groupings you have to use separate caches.
    Can you post a sample showing the problem for the first bit? Are you using formulas to determine what to format?
    Hi Rory,

    Stripped down version, look at the Holiday tab, there are conditional formats, I set the format to any range so the colour kick in, as soon as I do a refresh the conditional format range changes.

    Looks like I cannot post the file, the upload fails even though the size is only 3 meg zipped, can you send me an email address so I can mail it directly to you.

    Regards

    Mike
    Last edited by mikeyt; 2011-02-01 at 13:32.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You should be able to use the pivot table wizard (select a cell in the PT and press Alt+DP) to adjust the source range of the tables. Add a row to each, then remove it and you should, I think, get a separate cache. If you are prompted about saving memory, choose No (or you get the cache sharing again). I will PM you an email address for the file.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Apparently you don't accept PMs so I can't send you my email address.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Sorry for the delay, my email address is mike.towns@xerox.com

    Regards

    Mike

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    When you specified the formatting rules, did you set it to apply to 'all cells showing Sum of Days values'?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory,

    I did, but whatever I set the range for the conditional formating to it worked fine, as soon as I refreshed the data the formating disappeared.

    Regards

    Mike

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It works for me. The CF dialog should look like this when you review it (note the 'Applies to' part).
    Attached Images Attached Images
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey Rory,

    Thanks, I misunderstood your previous comment, I did not use "Sum of Days", sorry about that, but it work great now thanks

    Regards

    Mike

Posting Permissions

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