Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional formatting | Excel Discussion (Misc queries) |