Data Validation

B

braddy77

I am using this formula in a cell for data validation

=IF(C31="DIRECT BUSINESS",C34=0,C34<25)

If C31 = "DIRECT BUSINESS", I Can only enter 0 in C34. this bit works
fine.

However if C31 does not contain "DIRECT BUSINESS", I want to limit C34
to to a maximum of 25 this bit does not work.

Any ideas please.

Braddy:confused:
 
D

Debra Dalgleish

In the data validation dialog box, remove the check mark from "Ignore
blank", then click OK
 
B

braddy77

HI Debra

Tried what you suggested but it still allows me to enter more than 25

Thanks for the reply

Braddy
 
B

Bill Ridgeway

It looks to me that you are trying to say IF C31 has the text Direct
business THEN C34 should be 0 ELSE C34 should be <25.

I'm afraid you can't POKE a value into a cell. You need a formula in C34
that checks what is in C31 and returns the required value and that will have
to be an absolute value (or text <25) not something less than 24.

Regards.

Bill Ridgeway
Computer Solutions
 
D

Debra Dalgleish

Is the data validation on cell C34?
HI Debra

Tried what you suggested but it still allows me to enter more than 25

Thanks for the reply

Braddy
Click to expand...
 
B

braddy77

Hi Bill

Thanks for the reply.

But the user need to be able to enter anything frome 1 to 25 with a max
of 25 if C31 does not contain "DIRECT BUSINESS"


Braddy
 
B

Bill Ridgeway

Braddy wrote <<the user need to be able to enter anything from 1 to 25 with
a max > of 25 if C31 does not contain "DIRECT BUSINESS">>

If this is the case I would suggest a helper column with the formula -

IF(AND(C31<>"DIRECT BUSINESS",C34>25),"Error","OK")

IF C31 has anything other than the text Direct business AND C34 has a value
greater than 25 returns a message ERROR ELSE the message OK. This serves as
a visual reminder.

Regards.

Bill Ridgeway
Computer Solutions
 
D

Diva

In C34 you enter the validation formula as =OR(AND(C31="DIRECT
BUSINESS",C34=0),AND(C31<>"DIRECT BUSINESS",C34<25)) this will work
 
D

Diva

In Cell C34 enter validation Formula =IF(C31="DIRECT
BUSINESS",C34=0,AND(C34<25,C34>0)) A slight change in what you were
doing
Diva
 
D

Diva

I am talking about validation formula not Cell formula. Tha above said
formula is to be entered as a validation condition, not for entering in
a cell.
 
Top