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

--
Biff
Microsoft Excel MVP


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

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 (MVP - Excel)


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


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

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&"*","*J AN*MAR*MAY*JUL*SEP*NOV*"))

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


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

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.

"Rick Rothstein" wrote:

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&"*","*J AN*MAR*MAY*JUL*SEP*NOV*"))

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


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

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?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
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&"*","*J AN*MAR*MAY*JUL*SEP*NOV*"))

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


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

Good point! Maybe these formulas instead...

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

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

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
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?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
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&"*","*J AN*MAR*MAY*JUL*SEP*NOV*"))

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


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

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.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


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

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 (MVP - Excel)

Thanks for the info and all your help guys.

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


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

This ought to avoid false hits:
=AND(LEN($AK$2)=3,SEARCH($AK$2&".","JAN.MAR.MAY.JU L.SEP.NOV."))

The extra "." is to avoid false hits like "ARM".

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.

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

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

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?

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

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

"Loadmaster" wrote:

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?

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

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.


--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
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

T. Valko, to answer your questions on if these are user generated formulas is
you gave me the two array formulas. The one in A3 is:

=LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1))))

and, the one in AK2 is:

=INDEX(C2:C13,MATCH(1E+100,INDEX(D2:AJ13,0,MAX(IF( D2:AJ13<"",COLUMN(D2:AJ13)-COLUMN(D2)+1)))))

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
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

Ok, that helps to settle all of our "yeah, but if..." scenarios.

This went from a conditional formatting question to a data validation
contest.

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
T. Valko, to answer your questions on if these are user generated formulas
is
you gave me the two array formulas. The one in A3 is:

=LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1))))

and, the one in AK2 is:

=INDEX(C2:C13,MATCH(1E+100,INDEX(D2:AJ13,0,MAX(IF( D2:AJ13<"",COLUMN(D2:AJ13)-COLUMN(D2)+1)))))

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
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

"T. Valko" wrote...
....
Alternatives:

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

Good point.

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

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

These allow whitespace in AK2. If that's not OK, then change the
second cell
references to SUBSTITUTE($AK$2," ","%").

You need to use an OR function: =OR($AK$2="JAN",$AK$2="MAR",$AK$2="MAY",$AK$2="JUL ",$AK$2="SEP",$AK$2="NOV")=TRUE that'll work for the Blue one, same idea for the green one.