custom data validation

[Gareth]

I did post a similar question yesterday but it may have been too confusing
and I did not get an answer to suit my needs. I have decided to simplify my
question:
I have 3 columns (A, B and C), I need to add data validation to ensure dates
are entered with the following rules:
1. A2 cannot be greater than B2 or C2
2. B2 cannot be greater than C2 or less than A2
3. C2 cannot be less than A2 or B2
C2 can be an empty cell.
All dates must be >= 01/01/2007
Thanks in advance.
Gareth

 

[Bob Phillips]

Maybe

=AND(IF(COLUMN(K2)>COLUMN($K2),SUMPRODUCT(--((K2<=L2:$P2)+(L2:$P2="")))=COUNTA(L2:$P2)+COUNTBLANK(L2:$P2),TRUE),SUMPRODUCT(--((K2<=L2:$P2)+(L2:$P2="")))=COUNTA(L2:$P2)+COUNTBLANK(L2:$P2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gareth]

Maybe

=AND(IF(COLUMN(K2)>COLUMN($K2),SUMPRODUCT(--((K2<=L2:$P2)+(L2:$P2="")))=COUNTA(L2:$P2)+COUNTBLANK(L2:$P2),TRUE),SUMPRODUCT(--((K2<=L2:$P2)+(L2:$P2="")))=COUNTA(L2:$P2)+COUNTBLANK(L2:$P2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob
Many thanks for this, it appears to do what it is supposed to. Is there any
way to incorporate >=01/01/2007 in it?
Gareth

 

[Bob Phillips]

=AND(K2>=--"2007-01-01",IF(COLUMN(K2)>COLUMN($K2),SUMPRODUCT(--((K2<=L2:$P2)+(L2:$P2="")))=COUNTA(L2:$P2)+COUNTBLANK(L2:$P2),TRUE),SUMPRODUCT(--((K2<=L2:$P2)+(L2:$P2="")))=COUNTA(L2:$P2)+COUNTBLANK(L2:$P2))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)