Conditional Formatting

[Ellen G]

Hi there --

I am setting a 3-level conditional format and the 3rd level is not working.
It includes a 2-part OR statement -- if I put either of the OR items in
individually, the conditional format works. However, the complete OR
statement only works on one portion of the OR. Here's what I'm trying to
accomplish and I have inserted the 3 conditional format statements below.

Col A (Recruiter); Col C (Insertion Date); Col O (Date Phone Scr.
Compelted); Col Q (F2F Completed or N/A); Col 2 (References Completed)

Column A should turn the appropriate color based on the conditional
formatting (if.

Bright Yellow -- Condition 1
=AND(NOT(ISBLANK(C9)),(C9+30)<=TODAY(),ISBLANK(O9))

Blue -- Condition 2
=AND(NOT(ISBLANK(O9)),(O9+30)<=TODAY(),ISBLANK(Q9))

Green -- Condition 3
=OR(AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)),AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)))

Any help someone can provide would be most appreciated.

Ellen

 

[Paul H]

Hi there --

I am setting a 3-level conditional format and the 3rd level is not working.
It includes a 2-part OR statement -- if I put either of the OR items in
individually, the conditional format works. However, the complete OR
statement only works on one portion of the OR. Here's what I'm trying to
accomplish and I have inserted the 3 conditional format statements below.

Col A (Recruiter); Col C (Insertion Date); Col O (Date Phone Scr.
Compelted); Col Q (F2F Completed or N/A); Col 2 (References Completed)

Column A should turn the appropriate color based on the conditional
formatting (if.

Bright Yellow -- Condition 1
=AND(NOT(ISBLANK(C9)),(C9+30)<=TODAY(),ISBLANK(O9))

Blue -- Condition 2
=AND(NOT(ISBLANK(O9)),(O9+30)<=TODAY(),ISBLANK(Q9))

Green -- Condition 3
=OR(AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)),AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)))

Any help someone can provide would be most appreciated.

Ellen

Typically I find that if the order of the conditions are changes to reflect
the most likely condition first then the second then the third the
conditional formating will work. Once a condition is met, in say condition
one, the program will no longer look at condition two and/or three.

 

[Ellen G]

Thanks, Paul. I've tried rearranging the conditional formatting. Nothing
solves the problem so far. What I am finding is that the OR statement in the
3rd conditional format is simply not working. Even if I delete statements 1
and 2 and it is the only conditional format statement, it does not work. If I
use either piece of the OR statement by itself, it works. The OR combination
does not. HELP.

Ellen

 

[David Biddulph]

The condition OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA") seems strange.
Isn't that always true, because it can't be equal to "N/A" and also equal to
"NA", so it has to be unequal to at least one of those? Note also that
those string tests are not case sensitive, so you don't need the pairs.

If you are querying the outer OR test, the best bet is to break it down into
manageable chunks.
Set up a cell with
=OR(AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)),AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)))then a pair of cells with=AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)) and=AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)) respectively,and look at the TRUE or FALSE results from those.If you are struggling with one of those individual parts, then break thatdown again in a similar manner.If you are still struggling, then tell us what you have in Q9, S9, and O9,what result you are getting, and what you expected. Just saying "it doesnot work" doesn't help us to diagnose your problem.--David Biddulph"Ellen G" <[email protected]> wrote in messageThanks, Paul. I've tried rearranging the conditional formatting. Nothing> solves the problem so far. What I am finding is that the OR statement inthe> 3rd conditional format is simply not working. Even if I delete statements1> and 2 and it is the only conditional format statement, it does not work.If I> use either piece of the OR statement by itself, it works. The ORcombination> does not. HELP.>> Ellen>> "Paul H" wrote:>>>>>>> "Ellen G" wrote:>>>> > Hi there -->> >>> > I am setting a 3-level conditional format and the 3rd level is notworking.>> > It includes a 2-part OR statement -- if I put either of the OR items in>> > individually, the conditional format works. However, the complete OR>> > statement only works on one portion of the OR. Here's what I'm tryingto>> > accomplish and I have inserted the 3 conditional format statementsbelow.>> >>> > Col A (Recruiter); Col C (Insertion Date); Col O (Date Phone Scr.>> > Compelted); Col Q (F2F Completed or N/A); Col 2 (References Completed)>> >>> > Column A should turn the appropriate color based on the conditional>> > formatting (if.>> >>> > Bright Yellow -- Condition 1>> > =AND(NOT(ISBLANK(C9)),(C9+30)<=TODAY(),ISBLANK(O9))>> >>> > Blue -- Condition 2>> > =AND(NOT(ISBLANK(O9)),(O9+30)<=TODAY(),ISBLANK(Q9))>> >>> > Green -- Condition 3>> >=OR(AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)),AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)))>> >>> > Any help someone can provide would be most appreciated.>> >>> > Ellen>>>> Typically I find that if the order of the conditions are changes toreflect>> the most likely condition first then the second then the third the>> conditional formating will work. Once a condition is met, in saycondition>> one, the program will no longer look at condition two and/or three.

 

[Ellen G]

Thanks, David -- you're probably right. However, I don't think that is the
problem. If I simply use one piece of the major OR statement (either piece)
it does what it should. The combination is what breaks. In other words if I
simply use:

=AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9))

this provides the desired results.

If I simply use:

=AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9))

this provides the desired results.

The combination doesn't work:

=OR(AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)),AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)))

I can't figure out why.

Ellen

The condition OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA") seems strange.
Isn't that always true, because it can't be equal to "N/A" and also equal to
"NA", so it has to be unequal to at least one of those? Note also that
those string tests are not case sensitive, so you don't need the pairs.

If you are querying the outer OR test, the best bet is to break it down into
manageable chunks.
Set up a cell with
=OR(AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)),AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)))then a pair of cells with=AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)) and=AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)) respectively,and look at the TRUE or FALSE results from those.If you are struggling with one of those individual parts, then break thatdown again in a similar manner.If you are still struggling, then tell us what you have in Q9, S9, and O9,what result you are getting, and what you expected. Just saying "it doesnot work" doesn't help us to diagnose your problem.--David Biddulph"Ellen G" <[email protected]> wrote in messagenews:[email protected]...> Thanks, Paul. I've tried rearranging the conditional formatting. Nothing> solves the problem

so far. What I am finding is that the OR statement inthe> 3rd conditional format is simply not working. Even if I delete statements1> and 2 and it is the only conditional format statement, it does not work.If I> use either piece of the OR statement by itself, it works. The ORcombination> does not. HELP.>> Ellen>> "Paul H" wrote:>>>>>>> "Ellen G" wrote:>>>> > Hi there -->> >>> > I am setting a 3-level conditional format and the 3rd level is notworking.>> > It includes a 2-part OR statement -- if I put either of the OR items in>> > individually, the conditional format works. However, the complete OR>> > statement only works on one portion of the OR. Here's what I'm tryingto>> > accomplish and I have inserted the 3 conditional format statementsbelow.>> >>> > Col A (Recruiter); Col C (Insertion Date); Col O (Date Phone Scr.>> > Compelted); Col Q (F2F Completed or N/A); Col 2 (References Completed)>> >>> > Column A should turn the appropriate color based on the conditional>> >

 

[David Biddulph]

As I said, if you don't understand, you'll need to break it down into
manageable chunks, and if you want us to help you'll have to tell us what
the 3 input values to the formula are, what result you've got, and what you
expected.
--
David Biddulph

Thanks, David -- you're probably right. However, I don't think that is the
problem. If I simply use one piece of the major OR statement (either
piece)
it does what it should. The combination is what breaks. In other words if
I
simply use:

=AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9))

this provides the desired results.

If I simply use:

=AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9))

this provides the desired results.

The combination doesn't work:

=OR(AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9)),AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9)))

I can't figure out why.

Ellen

then a pair of cells with
=AND(NOT(ISBLANK(Q9)),OR(Q9<>"N/A",Q9<>"n/a",Q9<>"na",Q9<>"NA"),(Q9+30)<=TODAY(),ISBLANK(S9))
and
=AND(NOT(ISBLANK(O9)),OR(Q9="N/A",Q9="n/a",Q9="na",Q9="NA"),(O9+30)<=TODAY(),ISBLANK(S9))
respectively, and look at the TRUE or FALSE results from those.
If you are struggling with one of those individual parts, then break that
down again in a similar manner.
If you are still struggling, then tell us what you have in Q9, S9, and
O9,what result you are getting, and what you expected. Just saying "it does
not work" doesn't help us to diagnose your problem.
--David Biddulph