I’m having a problem with this; in my pivot table I’ve got conditional formatting applied to all cells in the table. When I go into ‘manage rules’, I can clearly see the cell range as “$A$5:$g$9999”…yet if I filter the table, or refresh it (even when the underlying data hasn’t changed), the cell range changes to something bizarre like "$a$5:$b$9999; $c$100:$g9999). Is there something I can do to make this stop happening? I can’t figure out why on Earth this is happening in the first place. But the same formatting appears on 12 different tabs (because my manager insists everything and anything be on a seperate tab for some reason). I don’t relish the idea of having to fix the cell range on every tab every time the data has to be updated…anyone ever come across this before?
I’ve never combined Pivot Tables with conditional formatting, but the problems you’re describing don’t overly surprise me. Pivot Tables do funny things format-wise.
Options:
-
Ditch the pivots and go with SumIfs or other lookup from the base data, so you don’t have to update the conditional formatting. Depends on how much data you have to show.
-
Copy/paste values from your pivots to set templates with the conditional formatting. Not so much fun, but you could create a macro.
-
Create macros to Refresh the Pivot data and then redo the conditional formatting (if this works, it may be the best option) Hit record, do the task once perfectly, and then run the macro when ever you need to update.
thanks—it has to be pivot table, 'cause that’s what the boss insists on. I’ll probably end up writing a macro, but I was just hoping there was something simple I didn’t know about.