# Data Validation IF Question

#### excelbytes

##### Board Regular
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.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### kweaver

##### Well-known Member
Code:
``=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

##### MrExcel MVP, Moderator

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

#### excelbytes

##### Board Regular
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

##### Well-known Member

Not my experience:

ForScott_re_SPILL.xlsm
ABCD
1150200FALSE
2700500TRUE
3200100300FALSE
4200500300FALSE
5600500300TRUE
6600500123FALSE
7100500123FALSE
8200999FALSE
Sheet3
Cell Formulas
RangeFormula
D1:D8D1=IF(ISNUMBER(B1),AND(C1<B1,C1<=A1,MOD(C1,100)=0,C1>=100),AND(C1<=A1,MOD(C1,100)=0,C1>=100))

#### excelbytes

##### Board Regular
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

##### MrExcel MVP, Moderator
Remove the tick from the 'Ignore blank' box in the Data Validation dialog.

Replies
16
Views
130
Replies
3
Views
143
Replies
3
Views
100
Replies
8
Views
256
Replies
4
Views
91

1,141,070
Messages
5,704,112
Members
421,327
Latest member
Msh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back