How do I use a conditional (IF) statement in Data Validation?

[Guest]

In column A I have numerical data, e.g. 50000. In column B I have a drop-down
box so the user may select either X,Y or Z. The user may also leave column B
blank. The user will input a number into column C.

In column C I am trying to set up Data Validation, as a decimal with min and
max allowed values. The MIN is always 0. The MAX is dependent upon column B.
IF column B is blank, then the MAX allowed in column C = column A * 0.06 (or
may be left blank).
IF column B = X, Y or Z (is not blank), then the MAX allowed in column C = 0
(or may be blank)

I have tried to put an IF conditional statement into the MAX field in data
validation but unsuccessful. It understands the MAX=0 but doesn't validate if
column B is blank and allows me to input any huge number. Any suggestions or
other ways to validate the data?

Thanks,
Linda

 

[Debra Dalgleish]

Select cell C2, and choose Data>Validation
From the Allow list, choose Custom
In the formula box, type:
=AND(C2>=0,IF(B2="",C2<=A2*0.06,C2=0))
Remove the check mark from Ignore blanks
Click OK

 

[Guest]

Hi Debra,
Thank you - it worked. Actually, what I was missing was unchecking the
Ignore Blanks checkbox. In my original formula, when I did this it also
worked correctly.
Appreciate your help!
Linda

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it helped.