OR(AND(Conditional Formatting Challenge

[ChuckRobert]

I've been unable to figure out the correct way to detail a conditional format. In my project -
- D61 is linked to another worksheet to obtain a name
- E61 is blank unless a job assignment is selected for that person (list data validation)
- Range D279:D310 displays the names of those with special circumstance that may affect what assignment they may be given.
- Range D364:D533 displays the names who are not available on a given day.

For conditional format 1 to display yellow, I need it to check that

- D61<>"" - (Name is Displayed) - True
- E61="" - (No assignment is selected Yet) - True
- The name in D61 is not listed in D364:D533 - (The individual is not gone today) - True
OR
- The name in D61 is not listed in in D364:D533 - (The individual is not gone today) - True
- The name in D61 is listed in D279:D310 - (Individual has special circumstance that may affect assignment) - True

Here is where I'm at now:

=OR(AND(D61<>"",E61="",COUNTIF(D$364:D$533,D61)<1),COUNTIF(D$364:D$533,D61)<1,COUNTIF(D$279:D$310,D61)>=1)

The problem seems to be in referencing the COUNTIF(D$364:D$533,D61)<1), as even if the name in D61 is not listed in D$364:D$533, it does not display as True and turn the cell yellow when the other conditions are met. D$364:D$533, and D$279:D$310 are linked from my calendar products and are in "General" format. Can anyone offer a suggestion?

 

[barry houdini]

=OR(AND(D61<>"",E61="",COUNTIF(D$364:D$533,D61)<1),COUNTIF(D$364:D$533,D61)<1,COUNTIF(D$279:D$310,D61)>=1)

I don't really understand this. You have 4 conditions, let's call them A, B, C and D. Your formula effectively says

=OR(AND(A,B,C),C,D)

The AND is redundant because C being TRUE will make the OR TRUE so your formula can be shortened to

=OR(C,D)

i.e.

=OR(COUNTIF(D$364:D$533,D61)<1,COUNTIF(D$279:D$310,D61)>=1)

....but I assume that isn't what you really want. for an AND to be TRUE every element must be TRUE, any single test within an OR makes the OR TRUE, is that how you expect it to be?

 

[ChuckRobert]

Barry,
Thanks for help! This works great.

=OR(AND(D61<>"",E61="",COUNTIF(D$364:D$533,D61)<1),COUNTIF(D$279:D$310,D61)>=1)


Chuck