Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Conditional Formatting with Pivot Table (multiple criteria) RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello all, Where can I post the question below. I initially posted in the Microsoft community and a moderator replied back asking me to post in this site instead. So far I have not seen an option where i can post my question as all the forums are very specific.

    Thanks,

    Victor

    -------------------------------------------------------------------------------------------------------------------------------Hello, I would like to know if there is an easier way when it comes to update conditional formatting in an excel pivot table. Right now I'm using formulas because that is the only way I know the CF will remain in a pivot tbl after refreshing the data.  

    So, I have 4 variables:

    1. Employee_Type (Internal or External)

    2. Function (Billing, Repair, W, X, Y, Z)

    3. KPIName (Aht, Xfr, Fcr, Voc, .............)

    4. KPI_Average (this is the actual % or number, the score for an specific timeframe)

    Billing Repair W X Y Z
    Aht 651.99 546.84 643.73 836.05 589.63 666.51
    Xfr 7.80% 19.95%
    Fcr 75.31% 76.62%
    Voc 68.03% 84.68%
    .........

    The formula within the conditional formatting points at these variables and depending on the value, it makes the cell either green or red)

    Ex.

    =AND($E$37="INTERNAL", E$41="Billing",E$44>500)  RED CELL 

    =AND($E$37="INTERNAL", E$41="Billing",E$44<=500)   GREEN CELL

    As you can see, for each combination of variables i need to create a pair of CF. 

    I have been playing with other formulas just to see if I can accomplish what i want with less effort.

    I created the below table and tried to use this formula but CF is giving me an error  "We found a problem with this formula. trying clicking Insert Function on the Formulas tab to fix it............

    =(SUMIFS(TblCF[Goal],TblCF[Function],E$7,TblCF[KpiName],$B16,TblCF[Employee_Type],$E$3)>E$44)

    Function Employee_Type KpiName Goal
    Billing INTERNAL AHT 500
    Repair INTERNAL AHT 600
    Billing  INTERNAL FCR 80%
    Repair INTERNAL FCR 70%
    Billing  INTERNAL XFR 20.5%
    Repair INTERNAL XFR 19%
    Billing  INTERNAL VOC 80%
    Repair INTERNAL VOC 83%

    Any help is greatly appreciated, even a solution in VBA will work.

    Friday, September 30, 2016 12:06 PM

Answers