unions, intersections or array constants

[Loadmaster]

I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up with
"You may not use unions, intersections or array constants for conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is there a
solution to make this work?

 

[T. Valko]

Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<>"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<>"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

 

[Rick Rothstein]

One thing to watch out for in Biff's formulas... if your entries are not
well controlled, there is a remote possibility of getting false hits. For
example, if your user enters MarMay in AK2, the that cell will turn blue. As
structured, if your user types in any substring from "JANMARMAYJULSEPNOV"
(such as the MarMay I used) or from "FEBAPRJUNAUGOCTDEC", then those
substrings will be considered a hit.

 

[Rick Rothstein]

If your entries are not well controlled and if you want to protect against
the problem I outlined in my previous posting, then these formulas should
work for you...

For Blue
===============
=AND($AK$2<>"",$AK$2<>"*",SEARCH("*"&$AK$2&"*","*JAN*MAR*MAY*JUL*SEP*NOV*"))

For Green
===============
=AND($AK$2<>"",$AK$2<>"*",SEARCH("*"&$AK$2&"*","*FEB*APR*JUN*AUG*OCT*DEC*"))

 

[Loadmaster]

Thank-you T.Valko you have never let me down on a formula, My only problem is
that excel 2003 only allows you to put three conditions. Here should be an
easy one for you. I am looking to combine a formula that if < 30 or > 300 to
turn red. Presently I have them as two conditional formats in cell A5?

 

[Loadmaster]

Thank-you Rick, It worked also, I guess I will remember the day my
spreadsheet was completed on my 48th Birthday when the last conditional
format <30 or >300 is complete.

 

[Loadmaster]

Hey, I figured this one out myself. If cell value is not between 30 and 300
to format red. Thanks for all your help

 

[T. Valko]

if your user enters MarMay in AK2

I wonder what the odds of that happening are? We don't even know if these
are user entered, they may be formula results.

 

[T. Valko]

If your entries are not well controlled and if you want to protect against

the problem I outlined in my previous posting, then these formulas should
work for you...

Or not. I only tried to break the first one but the second one will break
just as well.

If AK2 = Jan*Mar*May the format is applied. So, what do you think the odds
of that happening are?

 

[Rick Rothstein]

if your user enters MarMay in AK2

I wonder what the odds of that happening are?

I used MarMay as but one possible example... any substring in your you
'search' string will cause a false positive... for example, the letter M by
itself.

We don't even know if these are user entered,
they may be formula results.

That is why I said "if your entries are not well controlled".

 

[Rick Rothstein]

Good point! Maybe these formulas instead...

=AND($AK$2<>"",NOT(ISNUMBER(FIND("*",$AK$2))),SEARCH("*"&$AK$2&"*","*JAN*MAR*MAY*JUL*SEP*NOV*"))

=AND($AK$2<>"",NOT(ISNUMBER(FIND("*",$AK$2))),SEARCH("*"&$AK$2&"*","*FEB*APR*JUN*AUG*OCT*DEC*"))

 

[Glenn]

Good point! Maybe these formulas instead...

=AND($AK$2<>"",NOT(ISNUMBER(FIND("*",$AK$2))),SEARCH("*"&$AK$2&"*","*JAN*MAR*MAY*JUL*SEP*NOV*"))


=AND($AK$2<>"",NOT(ISNUMBER(FIND("*",$AK$2))),SEARCH("*"&$AK$2&"*","*FEB*APR*JUN*AUG*OCT*DEC*"))

Why not something like this:

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

 

[T. Valko]

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

ISODD uses the ATP. Excel will complain. I'd use MOD instead. But, I'd like
to know if these are formula results or user entered. I went under the
assumption they were formula results as they were presented in uppercase.

 

[T. Valko]

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

If AK2 is a number the format is applied based on the month number of the
date serial number.

 

[T. Valko]

If AK2 is a number...

....from 1 to 31...

 

[Harlan Grove]

Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<>"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<>"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

....

Quibble: these could return false positives for invalid entries in
cell AK2, e.g., if AK2 evaluated to "ARM", it'd be formatted blue.

Alternatives:

blue: =MOD(MONTH($AK$2&"-1"),2)=1

green: =MOD(MONTH($AK$1&"-1"),2)=0

 

[Glenn]

ISODD uses the ATP. Excel will complain. I'd use MOD instead. But, I'd like
to know if these are formula results or user entered. I went under the
assumption they were formula results as they were presented in uppercase.

Right, so some variation of this:

=MOD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")),2)

The OP seems to be saying that the data in AK2 is text, but if not, than you
could drop the DATEVALUE() and I would just use something like this:

=MOD(MONTH($AK$2))

 

[Glenn]

Right, so some variation of this:

=MOD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")),2)

The OP seems to be saying that the data in AK2 is text, but if not, than
you could drop the DATEVALUE() and I would just use something like this:

=MOD(MONTH($AK$2))

Obviously missing the ",2" in that last MOD()...

=MOD(MONTH($AK$2),2)

 

[T. Valko]

Quibble

This post has turned into a can-o-worms!

We (I) need to know if these are user entered or formula generated.

Alternatives:

If the cell contains a number from 1 to 12 one or the other formats is
applied.

 

[Loadmaster]

Thanks for the info and all your help guys.

I wonder what the odds of that happening are? We don't even know if these
are user entered, they may be formula results.