Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 12th 05, 09:55 PM
LindaB
 
Posts: n/a
Default 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   Report Post  
Old August 12th 05, 11:52 PM
Debra Dalgleish
 
Posts: n/a
Default

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   Report Post  
Old August 13th 05, 12:38 AM
LindaB
 
Posts: n/a
Default

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


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

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional format based on data from another column Ellie Excel Discussion (Misc queries) 3 May 14th 08 05:23 PM
How can I use HYPERLINK in a conditional statement between sheets Mikey Excel Worksheet Functions 1 May 3rd 05 08:07 PM
Conditional Statement with ANDs Stephen Excel Discussion (Misc queries) 2 April 11th 05 04:41 PM
Conditional Formating on calculated data in a pivot table David Excel Worksheet Functions 1 March 13th 05 03:16 PM
=IF Statement formula or conditional formatting Abi Excel Worksheet Functions 6 January 18th 05 01:06 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017