unions, intersections or array constants

  • Thread starter Thread starter Loadmaster
  • Start date Start date
L

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?
 
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"))
 
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.
 
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*"))
 
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?
 
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.
 
Hey, I figured this one out myself. If cell value is not between 30 and 300
to format red. Thanks for all your help
 
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.
 
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...
Click to expand...

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?
 
if your user enters MarMay in AK2
I wonder what the odds of that happening are?
Click to expand...

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.
Click to expand...

That is why I said "if your entries are not well controlled".
 
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*"))
 
Rick said:
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*"))
Click to expand...


Why not something like this:

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))
 
=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.
 
=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 said:
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"))
Click to expand...
....

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
 
T. Valko said:
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.
Click to expand...


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 said:
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))
Click to expand...


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

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

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

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

If the cell contains a number from 1 to 12 one or the other formats is
applied.
 
Thanks for the info and all your help guys.

T. Valko said:
I wonder what the odds of that happening are? We don't even know if these
are user entered, they may be formula results.
Click to expand...
 
Back
Top