Conditional Formatting in Pivot Table for each row based on value

[bvanscoy678]

Hello,


I applied conditional formatting with the attached document so that any time greater than 5 min 20 seconds would show a yellow side wards arrow. Works great, but instead I now need to set time for each pivot row category. I have a total of 7 different metrics which each one having a different time goal. Exammple EMS is 60 seconds, Fire is 80 seconds and so on. Is it possible to apply conditional formatting (yellow arrow) based on different values for each row? Although my example workbook is only showing data for one of the Engines, but I will have ENGA, ENGB, ENGC ect....


I hope this makes sense. Thanks. Brent


https://www.dropbox.com/s/wouerw073kk35f1/conditional%20formatting%202.xlsx

 

[Hui]

Bvanscoy678


You have applied the same CF to all rows 8:14


You can either apply Multiple CF's to the same ranges so that for example a Red arrow will present if some other criteria is met


Or


You can restrict the CF to a Row and then each will have different trigger points

EG: Row 10 Fire will be based on 80 seconds, but row 8 EMS may be based on something else


You can have multiple CF's over the same range and they are carried out in order so you need to get the order correct

 

[bvanscoy678]

Hui,


Yes, I did and that was the problem. As it turns out I only need to identify 4 categories with only 2 different times. EMS will be 60 seconds, while Fire/explosion/Hazmat will be 80 seconds. The other categories will not be addressed with conditional formatting.


I will have to practice this at work tomorrow. I should have explained that I did not upload the pivot table, but an example of what the pivot looks like. Since it is a pivot table, not every item will always have every category, I think I will need to apply it to the range. If I were using a formula I would use a nested if statement. Something like =if(A1=EMS,"Red Arrow",if(A1=Fire,"Blue Arrow","")) The syntax may not be correct, but I hope it gives you the idea. Red Arrow and Blue Arrow is just to signify the formatting.


Thanks and I'll check back in the morning with my progress. Brent

 

[Hui]

I don't believe that you can do Conditional Conditional Formats when you are applying them to Icons as you are trying to do


I'd suggest you go back to simply changing the background color of the cells


As the field positions could change with in a Pivot Table you will have to use something like the following


Select C8:C14 Ctrl E8:E14 Ctrl G8:G14 Ctrl I8:I14

Clear the existing CF's

Add a CF

Use a formula

=AND($A8="EMS",C8>0.003)

set a Cell Style

Apply


Add a new CF

Use a formula

=AND($A8="Fire",C8>0.004)

set a new Cell Style

Apply


etc


This is shown in your file here:

https://www.dropbox.com/s/nhgyk74k7pjtm08/conditional%20formatting%202%20Hui.xlsx

 

[bvanscoy678]

You are correct, I read up on it and you can not use relative references with the icon sets. I studied you file and formula and applied to my example, but I must have missed something. I copy/pasted values from my pivot and applied the formula to C5, but it doesn't change background and font colors. I changed the value of C5 to show the decimal just until I workout the problem.


I understand what to do with all the other categories, once I get it to work.


thanks for the help


https://www.dropbox.com/s/ki77268p116tyxl/hui%20follow%20up.xlsx

 

[bvanscoy678]

Hui,


I needed to add the TRIM function to my first column. That fixed it! Thanks!!