Conditional formatting in a pivot table

Hi there,

I've run into something for which I did yet not find a satisfactory solution.

I've a sheet with data, say: 3 main categories, which are subdivided, which are again subdivided. Then at the lowest level, I will have a column stating if that certain attribute is 'done'.

example_rawdata.PNG

Now I'll make a pivot table from this in order to have the collapse/uncollapse feature:

example.PNG

Now I want in the pivot table the colour be determined by its status as I have done manually in the picture above. For the lowest level this is easy, rule: =D4:D40="Done" and applied to: =C4:C40.

However, for the column 'sub' this is less straightforward, since the current lay-out is greatly simplified and I want it to be dynamically changing when I add or omit new entries.

The goal of this table is to easy have an overview of which attributes are not 'done', since this list will contain around a 500 attributes. Even when I collapse a level of detail.

Is there someone here with a smart solution to my problem? Any help would be greatly appreciated!

This proposal adds two columns to both the RawData and Pivot sheets.
On the RawData sheet Column E is populated using:

Formula:

=COUNTIFS(A$2:A$13,A2,B$2:B$13,B2)=COUNTIFS(A$2:A$13,A2,B$2:B$13,B2,D$2:D$13,"Done")

Column F is populated using: =COUNTIFS(A$2:A$13,A2)=COUNTIFS(A$2:A$13,A2,D$2:D$13,"Done")
The columns are named Sstatus and Mstatus respectively and the columns (E:F) are then added to the pivot table, although hidden by grouping.
The formatting on the pivot table is accomplished using formulas similar to: =AND(A4<>"",F4="False")
Let us know if you have any questions.

I imagine that you mean that you want to remove 'Sub' and 'SubSub' from the row labels field and maintain the conditional formatting. I feel that you would need something like structured references in the CF formula. As far as I know the 2010 version doesn't have that capability regarding a pivot table. Perhaps it is built into the 2016 version, if not this article on creating structured references for pivot tables via VBA may help.
A work around that might be useful would be group columns B:C on the Pivot sheet so that the 'Sub' and 'SubSub' fields can be hidden without changing the columns containing Sstatus and Mstatus.
Let us know if you have any questions.