unions, intersections or array constants

  • Thread starter Thread starter Loadmaster
  • Start date Start date
This ought to avoid false hits:
=AND(LEN($AK$2)=3,SEARCH($AK$2&".","JAN.MAR.MAY.JUL.SEP.NOV."))

The extra "." is to avoid false hits like "ARM".
 
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)))))
 
Ok, that helps to settle all of our "yeah, but if..." scenarios.

This went from a conditional formatting question to a data validation
contest.
 
T. Valko said:
If the cell contains a number from 1 to 12 one or the other formats is
applied.
Click to expand...
....

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," ","%").
 
Back
Top