INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close