View Single Post
  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MyVeryOwnSelf[_2_] MyVeryOwnSelf[_2_] is offline
external usenet poster
 
Posts: 143
Default unions, intersections or array constants

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