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

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

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 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
>
> 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 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
>>
>>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

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

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 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
>
> 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 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
>>
>>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

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 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

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 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
>
> 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 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
>>
>>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