Data Validation - Custom question (multiple conditions)

[lukin]

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
numbers (eg O6277305)

I have a sheet where users enter order numbers and I want to use custo
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) bu
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
legitimate formula but I don't get an error when entering something tha
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,
Luk

 

[Claus Busch]

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

 

[Spencer101]

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
numbers (eg O6277305)

I have a sheet where users enter order numbers and I want to use custo
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) bu
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
legitimate formula but I don't get an error when entering something tha
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 al
three conditions being met. So if you put in a reference that start
with a Z, is only 4 characters long and one of those is a space, th
validation error message will kick in.

Try using =OR() rather than =AND(

 

[lukin]

Hi Luke,

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

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

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

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

 

[FoulFoot]

Hi folks -

Similar question with the exact same title (multiple conditions), so
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,ISNUMBER(G10)+0)

It looks at the value entered in G10 and sees if it EITHER matches
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

Scot

 

[Claus Busch]

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,ISNUMBER(G10)+0))

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


Regards
Claus Busch

 

[FoulFoot]

I think I figured out what the problem was, though since I've moved o
with life, I haven't tried this out. Our source cell, G10, was formatte
as text, in order to both accommodate actual text as well as retai
leading zeroes. ISNUMBER can't parse text (even with the "+0" operator
which I thought worked). VALUE works in this case. So the workin
formula should be:

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

Scot

 

[sparikh95]

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

 

[sparikh95]

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