ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   How do I use a conditional (IF) statement in Data Validation? (https://www.excelbanter.com/excel-worksheet-functions/40252-how-do-i-use-conditional-if-statement-data-validation.html)

LindaB

How do I use a conditional (IF) statement in Data Validation?
 
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 DataValidation
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

LindaB wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


LindaB

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" wrote:

Select cell C2, and choose DataValidation
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

LindaB wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



Debra Dalgleish

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

LindaB wrote:
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" wrote:


Select cell C2, and choose DataValidation
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

LindaB wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



All times are GMT +1. The time now is 03:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com