Conditional Formatting not working with PIVOT table

[xVICTORx]

Hello All,

This is my first Post, I hope somebody can help me to get this to work. So, this is what I'm trying to accomplish:
I have this pivot tbl and I need to conditional format it. I've been using formulas for my other pivots but this one somehow is not working, as you can see from the file I uploaded, the pivot has been grouped by Function/Employee_Type.
Below is the logic I was using and is not working:

=AND($E$7="Three Product",$E$8="IN-HOUSE",E$18<73.5%) fill RED
=AND($E$7="Three Product",$E$8="IN-HOUSE",E$18>=73.5%) fill GREEN

=AND($E$7="Three Product",$E$8="BUSINESSPARTNER",E$18<75%) fill RED
=AND($E$7="Three Product",$E$8="BUSINESSPARTNER",E$18>=75%) fill GREEN

 

[NARAYANK991]

Hi ,

1. I am not sure that the rules you posted have been entered in the workbook ; there are too many rules and I have not checked , so I might be wrong.

2. E8 can never be BUSINESSPARTNER , since that text is in L8.

Narayan

 

[xVICTORx]

Hello Narayan,

1. Yes, I have tried different approaches with the CF. I think is not doing what I need because ROW 8 have 2 groupings (IN-HOUSE and BUSINESSPARTNER).
All my other Pivots that did not have 2 groupings in the same ROW are working fine.
2. You are right about this one, I meant to type <>"IN-HOUSE".

 

[NARAYANK991]

Hi ,

As far as I can see , there is no reason why the formula you posted should not work ; in fact , I do not see any reason why you should use E$18 , when E18 will work just as well , if not better.

If you can post all the rules that need to be applied , I am sure the nearly 75 rules that are in use for the pivot table can be reduced. A lookup table of all the conditions that apply and the resulting formats that need to be used if those conditions are satisfied will help.

Narayan

 

[xVICTORx]

Please see attached file. You will see that the CF that should apply when $E$8<>"IN-HOUSE" doesn't do what I need.
I have a goal tbls as well. i dont know if there is an easy way using a vlookup. There are so many conditions that I need to apply to this pvt .
1. $E$7="Repair" (this is a merged cell cause is in a pivot tbl)

2. $E$8="IN-HOUSE" (this is a merged cell cause is in a pivot tbl, this ROW have 2 groupings "IN-HOUSE" & "BUSINESSPARTNER")

3. values on E$18

 

[NARAYANK991]

Hi ,

I think there is some confusion over the formula :

E8 can never be BUSINESSPARTNER , which is why the following rule :

=AND($E$7="Repair",$E$8<>"IN-HOUSE",E$18<71%)

will never work.

See the attached file , where I have used a helper row ( row #1 , cells AA1 onwards ) to derive the threshold value. This simplifies the CF formula.

If you can list down all the conditions and the resulting threshold values for the various rows , we can use helper cells ( rows and columns ) to simplify the CF formulae , as well reduce their number.

Narayan

 

[xVICTORx]

Thank you Narayan, I found a solution!!!! I remove the subgrouping from the pivot table (in-house & partners) and let all in. I also add a vlookup to the formula to find in-house centers and it worked.