Data Validation IF Question

[excelbytes]

I am trying to use Data Validation to restrict inaccurate entries. The current issue I have is between columns A, B, and C. Here are the conditions for column C:
C must not be greater than A
C must be in multiples of 100
C minimum value must be 100
IF there is a value in B (B is optional), then C must be less than B, else just the first three conditions.

The logical formula would be:

=AND(C1<=A1,MOD(C1,100)=0,C1>=100,IF(ISNUMBER(B1),C1<B1))

I assume the last variable in the AND function is invalid for Data Validation. How can I accomplish this?

Thanks.

 

[kweaver]

What about this adjustment to your logic?

=IF(ISNUMBER(B1),AND(C1<B1,C1<=A1,MOD(C1,100)=0,C1>=100),AND(C1<=A1,MOD(C1,100)=0,C1>=100))

 

[Peter_SSs]

What happens if you just add this to your existing formula?

=AND(C1<=A1,MOD(C1,100)=0,C1>=100,IF(ISNUMBER(B1),C1<B1,TRUE))

 

[excelbytes]

I tried each of the following:
=IF(B1>0,AND(C1<B1,C1<=A1,MOD(C1,100)=0,C1>=100),AND(C1<=A1,MOD(C1,100)=0,C1>=100))
=AND(C1<=A1,MOD(C1,100)=0,C1>=100,IF(ISNUMBER(B1),C1<B1,TRUE))
=IF(ISNUMBER(B1),AND(C1<B1,C1<=A1,MOD(C1,100)=0,C1>=100),AND(C1<=A1,MOD(C1,100)=0,C1>=100))

They all work except when B1 is blank. Then they allow any entry into column C. I'm questioning if you can have an IF statement within a custom Data Validation. The formulas work outside of Data Validation, showing the correct TRUE and FALSE results, but not when I enter them in Data Validation.

 

[kweaver]

Not my experience:

ForScott_re_SPILL.xlsm
	A	B	C	D
1	150		200	FALSE
2	700		500	TRUE
3	200	100	300	FALSE
4	200	500	300	FALSE
5	600	500	300	TRUE
6	600	500	123	FALSE
7	100	500	123	FALSE
8	200		999	FALSE
Sheet3

Cell Formulas
Range	Formula
D1:D8	D1	=IF(ISNUMBER(B1),AND(C1<B1,C1<=A1,MOD(C1,100)=0,C1>=100),AND(C1<=A1,MOD(C1,100)=0,C1>=100))

 

[excelbytes]

kweaver,

I understand what you've done, and as I said previously, the formulas work outside of Data Validation, but not when I put it in the Data Validation Custom field (column E has FORMULATEXT for column D):

As you can see above, cells C1, C2, & C7 all result in a FALSE with the formulas entered in column D, but Data Validation still allows me to enter those values in column C:

 

[Peter_SSs]

Remove the tick from the 'Ignore blank' box in the Data Validation dialog.