Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35
Default Conditional Formatting

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default Conditional Formatting



"Ellen G" wrote:

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.
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35
Default Conditional Formatting

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

"Paul H" wrote:



"Ellen G" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Conditional Formatting

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" wrote in ... 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 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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35
Default Conditional Formatting

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

"David Biddulph" wrote:

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" wrote in ... 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
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.




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Conditional Formatting

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

"Ellen G" wrote in message
...
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


"David Biddulph" wrote:

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 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 02:02 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 0 January 15th 07 04:35 PM
Conditional Formatting that will display conditional data BrainFart Excel Worksheet Functions 1 September 13th 05 05:45 PM
Conditional Formatting Deb Excel Discussion (Misc queries) 7 March 6th 05 03:37 PM
conditional formatting bobf Excel Discussion (Misc queries) 1 March 1st 05 01:54 PM


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"