Conditional Formatting - Multiple Conditions

[sd2173]

First I am wondering if it is possible to insert text into a new cell if conditional formatting conditions are met, and second if it is possible to require two conditions be met in order to insert the text in a new cell.

I am trying to have say column C rows display an "X" if the column A = 10 and column B is < 6.

Not knowing how to insert text if conditions are met I have run the following conditions to display a color and pattern if met:

Condition 1:
Formula is: =($A1=10)
*Yellow highlight

Condition 2:
Formula is: =($E1<6)
*Line pattern

The issue I run into, not to mention the color/pattern does me no good, is I get yellow highlights for all "10's" and line patterns for all "<6's". Not both. This tells me it is reading the conditions seperate, but I don't know how to pair them.

Ideally when I figure this out I am planning to run the same conditions for the following:

A=10 and B<6
A=11 and B<12
A=12 and B<18

My thoughts are that if I continue to run different conditional formatting over the new column I want the "X" to be placed in I will remove previous formattings, so if these can be lumped into one formula or VBA macro it would be ideal.

Any suggestions appreciated, thanks!
SD

 

[manojrf]

Hi SD,

Enter this formula in column C1 , =IF(AND(A1=10,B1<6,B1<>0),"x","") . This formula will enter "x" in column C1 , if A1 =10 & B1<6 . I have put B1<>0,because if B1 is blank or 0 , the formula takes it as B1<6.

You change this as you need.

I hope this will help you

Regards , Manuel

 

[sd2173]

Thanks a ton Manuel, this works great! Is there a way to extend the formula to add "or" so I can include the conditions of =11 and <12, and =12 and <18?

Otherwise I have to run it 3 times and combine the 3 columns, which I dont know how to combine the columns into one by pasting the data without overwriting the existing x's.

Hope this makes sense, this is what I'm using now, changed for my data locations:

=IF(AND(D2=10,E2<6,D2<>0),"x","")
=IF(AND(D2=11,E2<12,D2<>0),"x","")
=IF(AND(D2=12,E2<18,D2<>0),"x","")

Any insight appreciated, thanks again!

 

[manojrf]

Hi,

Do you wanna combine all the 3 formulas you have given , to be applied into one cell ? If so, this is how it is done . =IF(AND(D2=10,E2<6,D2<>0),"x",IF(AND(D2=11,E2<12,D2<>0),"x",IF(AND(D2=12,E2<18,D2<>0),"x",""))) .

Check this out.

Regards, Manuel