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

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

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

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