Data Validation - Formula Creation

[Tweetie]

I would like the individual cells of C1 and D1 to allow me to input values up to 100.

The tricky part is that if D1 equals 100 I would like C1 to be less than 100 and vice versa, if C1 equals 100 then D1 should equal less than 100.

Ex. C1 = 100 & D1 = 10

This should never happen: C1 = 100 & D1 = 100

I would also like an error box to show when either of the cell values exceed 100.

I hope this makes sense.

[NBVC]

Try this Custom formula in Data Validation for C1:

=IF(C2=100,IF(C1<100,TRUE,FALSE),IF(C2<100,IF(C1<=100,TRUE,FALSE)))

and this one for C2:

=IF(C1=100,IF(C2<100,TRUE,FALSE),IF(C1<100,IF(C2<=100,TRUE,FALSE)))

You can create an error message within the Error Alert tab of Data Validation Window

[NBVC]

I think you can also use these simpler formulas instead

=OR((C2=100)*(C1<100),(C2<100)*(C1<=100))
and

=OR((C1=100)*(C2<100),(C1<100)*(C2<=100))