Data Validation IF Question

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
114
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
2,105
Office Version
  1. 365
  2. 2010
What about this adjustment to your logic?
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
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 11, 2014
Messages
114
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
2,105
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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
Joined
Dec 11, 2014
Messages
114
Office Version
  1. 365
Platform
  1. Windows
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):

DV IF.png


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:


DV IF 2.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
Remove the tick from the 'Ignore blank' box in the Data Validation dialog.
 
Solution

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top