Conditional Formats in Pivot Tables – Excel 2007

Topic

New Reply

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

Reply | Quote

Replies

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’?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Apparently you don’t accept PMs so I can’t send you my email address.

Reply | Quote

Hi Rory,

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

Regards

Mike

Reply | Quote

When you specified the formatting rules, did you set it to apply to ‘all cells showing Sum of Days values’?

Reply | Quote

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

Reply | Quote

It works for me. The CF dialog should look like this when you review it (note the ‘Applies to’ part).

Reply | Quote

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

Reply | Quote