CODEWITHSUNDEEP
excelexcel-2010conditional-formatting
FizzBuzz
FizzBuzz

Reputation: 568

Formatting a cell based on 2 different values

Using Excel 2010 and trying to work on some semi-complicated conditional formatting.
The spreadsheet is a roster system (don't ask why...the bosses insist it's the best).

I have 1 column which denotes the role for each person.
i.e. Person 1 - Level 1
Person 2 - Level 2
Person 3 - Level 1

And then I have a large area which denotes what hours they are working. Each cell is a half hour block, if the person is working, a 1 is placed in the block.

What I want to do is highlight work hours a certain color based on what role they are working. Something like green for Level 1, red for Level 2, etc., etc.

I was trying something like this:

=INDIRECT("D"&ROW())="Level 1"&VALUE=1

No luck though. I'm pretty sure the indirect portion works, because I already use it to set that person's entire row blue if their "role" is "un-available".

So yeah, if anyone could help me figure out how to do this, it would be much appreciated.

Upvotes: 1

Views: 7728

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

You simply need to add a Conditional Format rule for each Role, applying its unique colour.

Assuming you can split the name and role into seperate columns

Rule is a "Use a formula to determine which cells to format" type, in the general form of

=AND(Cell is 1, Role = RoleName)

Eg, if Roles are in column B, and cell to format is C2, for "Role 2"

=AND(C2<>"",$B2="Level 2")

Upvotes: 2

Related Questions