Data Validation Problem (Excel 200)

[braddy60]

I am using this formula 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.

I have also tried the following

=OR(AND(C31="DIRECTBUSINESS",C34=0),AND(C31<>"DIRE CT BUSINESS",C34<25))

=IF(C31="DIRECTBUSINESS",C34=0,AND(C34<25,C34>0))

But they all allow me to enter more than 25 in C34 If C31 does not contain "Direct Business"

Braddy

[HansV]

Does it help if you clear the "Ignore Blank" check box in the Data | Validation dialog?

[sdckapr]

It seems to work for me in XL2002. (see the attached file)

Steve

[HansV]

But Alan has Excel 200 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

If the "Ignore Blank" check box is ticked, the validation will not kick in if C31 is blank.

[braddy60]

HI Steve

I am left with egg on my face, because the cell was formatted to % <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Thank to you and Hans for your reply.

Braddy

[braddy60]

HI Hans

I have to save up and buy a later version <img src=/S/grin.gif border=0 alt=grin width=15 height=15> , please see my reply to Steve.

Many thanks once again

Braddy