Conditional Formatting one cell dependant on the combination of its value & a second cell value

[BeeBear2]

Hi,
Using MS Excel 2010.

I have an excel spreadsheet roster that I am trying to put some quick highlighting in to easily see if I'm missing a role for a particular day.


Basically, I have one Row per day, and several columns of names along the top, so under each name I add a role.
To the right of this, I have 2 columns that are calculations of the number of times a particular role is seen in that row.
Column X counts the number of "Probationary"s and Column Y counts the number of "S" (Supervisor)



Now, There can be anywhere between 0 and 4 "Probationary"s for a day, but as long as there is AT LEAST 1, I HAVE to add a Supervisor "S" Role.


My problem is trying to highlight where the "Supervisor" Count is ZERO, but only where the "Probationary" count is GREATER THAN ZERO.

EG
<Date> <person1> <person2> <person3> <person4> <person5> <TOTAL P> <TOTAL S>
<5 Jan> < P > < X > < P > < R > < T > < 2 > < 0 >
<6 Jan> < T > < X > < T > < R > < T > < 0 > < 0 >

(The real roster has about 25 people, so is harder to visually scan across, which is why I have the totals column)

For 5th Jan, the <TOTAL S> needs highlighting to show that I SHOULD have a Supervisor role (as there are 2 "Probationary")
For the 6th of Jan, even though <TOTAL S> is 0, there are no Probationary so thats ok.



Hope that all makes sense.



Hope someone can help.

 

[SkipVought]

Hi,

I'd first suggest converting your table to a Structured Table--Insert > Tables > Table. That way, as you add rows the CF will expand with your Structured Table.

Select the DATA in column TOTAL S. I assume that you have headings in row 1

Open the Conditional Format wizard

New rule > Use a formula

Formula: =AND(X2>0,Y2=0)

Of course, you must add an associated FORMAT of your choosing.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]