Remember Me?

#1
August 12th 05, 09:55 PM
 LindaB Posts: n/a
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

#2
August 12th 05, 11:52 PM
 Debra Dalgleish Posts: n/a

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

#3
August 13th 05, 12:38 AM
 LindaB Posts: n/a

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

#4
August 13th 05, 12:51 AM
 Debra Dalgleish Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Ellie Excel Discussion (Misc queries) 3 May 14th 08 05:23 PM Mikey Excel Worksheet Functions 1 May 3rd 05 08:07 PM Stephen Excel Discussion (Misc queries) 2 April 11th 05 04:41 PM David Excel Worksheet Functions 1 March 13th 05 03:16 PM Abi Excel Worksheet Functions 6 January 18th 05 01:06 PM

All times are GMT +1. The time now is 11:40 AM.