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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

When building a chart Dates are been included that are not in thedate range 1
Changing Sum Range Based on Date Chosen from Drop Down List 1
Functions/formula 7
Find longest consecutive winning streaks 7
YTD budget formula based on current actual input 3
Obtain a Year to Date result without all cells of data being fille 3
UNION and Crosstab query 5
lookup help. lookup result based on data in 2 columns 3

Back
Top