Excel - Data Validation - Custom question (multiple conditions)

Hi all, hoping someone can help me with a little trouble I am 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 does not begin with the letter "O"
b) It does not contain 8 characters
c) It contains any spaces

I have figured out how to do each of these by themselves (as below) but
I cannot 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 do not 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 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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

lukin;1601101 Wrote:

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()




--
Spencer101

Claus Busch;1601106 Wrote:

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.




--
lukin

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 am 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 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 is entered by itself:

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

The two validations will not work together with the OR operator.

This is driving me nuts!  Any help appreciated!

Scott




--
FoulFoot

Hi Scott,

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


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

I think I figured out what the problem was, though since I have moved on
with life, I have not tried this out. Our source cell, G10, was formatted
as text, in order to both accommodate actual text as well as retain
leading zeroes.  ISNUMBER cannot parse text (even with the "+0" operator,
which I thought worked).  VALUE works in this case.  So the working
formula should be:

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

Scott




--
FoulFoot