Data Validation

[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

 

[Debra Dalgleish]

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

 

[braddy77]

HI Debra

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

Thanks for the reply

Braddy

 

[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

 

[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

 

[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

 

[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

 

[braddy77]

Hi Bill

Thanks very much for your solution.

Braddy

 

[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

 

[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

 

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