Hi all, hoping someone can help me with a little trouble I'm having.

Order numbers in our system always begin with a letter "O" followed by 7 numbers (eg O6277305)

I have a sheet where users enter order numbers and I want to use custom data validation on this column so that you get an error if:
a) It doesn't begin with the letter "O"
b) It doesn't contain 8 characters
c) It contains any spaces

I have figured out how to do each of these by themselves (as below) but I can't seem to get them to work all together.
a) =LEFT(B3,1)="O"
b) =LEN(B4)=8
c) =B5=TRIM(B5)

I have tried the below, which data validation seems to accept is a legitimate formula but I don't get an error when entering something that breaks the rules:
=AND(LEFT(B3,1)="O",LEN(B4)=8,B5=TRIM(B5))

Is anybody able to give some advice on what I might be doing wrong?

Many thanks,
Luke

Hi Luke,

Using =AND() in this formula means the validation is dependent on all three conditions being met. So if you put in a reference that starts with a Z, is only 4 characters long and one of those is a space, the validation error message will kick in.

Try using =OR() rather than =AND()

Hi Luke,

Am Tue, 24 Apr 2012 06:14:20 +0000 schrieb lukin:

I have figured out how to do each of these by themselves (as below) but
I can't seem to get them to work all together.
a) =LEFT(B3,1)="O"
b) =LEN(B4)=8
c) =B5=TRIM(B5)

I have tried the below, which data validation seems to accept is a
legitimate formula but I don't get an error when entering something that
breaks the rules:
=AND(LEFT(B3,1)="O",LEN(B4)=8,B5=TRIM(B5))

you have to refer all conditions to the same cell:
=AND(LEFT(B3,1)="O",LEN(B3)=8,LEN(TRIM(B3))=LEN(B3 ))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus - that did the trick. I had figured out the individual ones on seperate cells and when I incorporated them into one formula I neglected to remember to make them all refer to the same cell - I feel rather stupid missing that.

But even if I had got the cell references right I was still missing a couple of the (LEN) parts. I tried this and it does exactly what I want, so thanks a lot for your help.

Hi folks --

Similar question with the exact same title (multiple conditions), so I decided just to reply here rather than create a duplicate post --

I'm trying to get this custom validation to work:

=OR(MATCH(G10,$G$93:$G$98,0),AND(LEN(G10)=6,ISNUMB ER(G10)+0))

It looks at the value entered in G10 and sees if it EITHER matches a list at G93-G98, OR is a six digit number.

The first part of the validation works fine.

The second part of the validation works only if it's entered by itself:

=AND(LEN(G10)=6,ISNUMBER(G10)+0)

The two validations won't work together with the OR operator.

This is driving me nuts! Any help appreciated!

Scott

Hi Scott,

Am Sun, 6 May 2012 22:04:31 +0000 schrieb FoulFoot:

=OR(MATCH(G10,$G$93:$G$98,0),AND(LEN(G10)=6,ISNUMB ER(G10)+0))

try:
=OR(ISNUMBER(G10)*(LEN(G10)=6),ISNUMBER(MATCH(G10, $G$93:$G$98,0)))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hello,

G10 and G93:G98 are not necessarily a number, so I removed that ISNUMBER. Didn't work. I also wasn't sure what the asterisk denoted, so I tried it both with it in, and replacing it with a comma. Still no dice.

I've decided to split the offending cell into two, and just force folks to enter numbers in one cell, and list-match the second cell. They'll get over it. :) Though I wonder if I stumbled upon a bug in Excel, since as far as I can see, my syntax is correct.

Thanks for the assistance!

Scott

On Monday, April 23, 2012 11:14:20 PM UTC-7, lukin wrote:
Hi all, hoping someone can help me with a little trouble I'm having.



Order numbers in our system always begin with a letter "O" followed by 7

numbers (eg O6277305)



I have a sheet where users enter order numbers and I want to use custom

data validation on this column so that you get an error if:

a) It doesn't begin with the letter "O"

b) It doesn't contain 8 characters

c) It contains any spaces



I have figured out how to do each of these by themselves (as below) but

I can't seem to get them to work all together.

a) =LEFT(B3,1)="O"

b) =LEN(B4)=8

c) =B5=TRIM(B5)



I have tried the below, which data validation seems to accept is a

legitimate formula but I don't get an error when entering something that

breaks the rules:

=AND(LEFT(B3,1)="O",LEN(B4)=8,B5=TRIM(B5))



Is anybody able to give some advice on what I might be doing wrong?



Many thanks,

Luke









--

lukin

Hi,

I have similar issue but different conditions.

1) serial number contains minimum 14 characters and maximum 15 characters
2) the first character may or may not be "S"

I can't seem to figure this out. Please help. Thanks.

SP









--

lukin