2 separate Conditional Formats for a Location

[jackson1990]

Hey everyone,

I've been searching around and can't quite piece together how to achieve this.

Problem:
I want to apply conditional formatting to a pivot table. This pivot table contains a list of names for the rows, and weeks on the columns. The values are the sum of hours. Data that is available but not showing on the table is Location. For these purposes we only have two locations that are relevant. US or UK. For US based names if the hours equal less then 40, it will flag the value cells as red. For UK names it will do the same but for 37.5 hours.


I don't believe this is possible flag certain locations one way and others another. So, would the best solution be two buttons at the top that change that formatting on the fly for the user? One button will flag everything less than 40 and the other everything less than 37.5. That way they can switch on the fly.

Appreciate any thoughts on this!

Thanks

 

[DanteAmor]

You can create the 2 conditional formats in the following way.


In the example yellow is for US and purple is for UK.

The formulas are the following:

=AND(G4<>"",G4<40,VLOOKUP($F4,$A:$B,2,0)="US")
=AND(G4<>"",G4<35,VLOOKUP($F4,$A:$B,2,0)="UK")



Applies to:
=$G$4:$J$7

-----

I attach my test file so you can see how it works.

https://www.dropbox.com/s/oeidlwqa0pzw0rc/CF us uk.xlsx?dl=0