Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default Data Validation - Custom question (multiple conditions)

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Data Validation - Custom question (multiple conditions)

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
  #3   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by lukin View Post
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()
  #4   Report Post  
Junior Member
 
Posts: 2
Default

Quote:
Originally Posted by Claus Busch View Post
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.
  #5   Report Post  
Junior Member
 
Posts: 3
Default

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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Data Validation - Custom question (multiple conditions)

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
  #7   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Claus Busch View Post
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
  #8   Report Post  
Junior Member
 
Posts: 3
Default

I think I figured out what the problem was, though since I've moved on with life, I haven't 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 can't 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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Data Validation - Custom question (multiple conditions)

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


  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Data Validation - Custom question (multiple conditions)

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
custom data validation on cells with data validation values AKrobbins Excel Worksheet Functions 2 June 21st 11 04:20 PM
3 Conditions for Data Validation for a List Anders Excel Programming 7 July 7th 09 12:26 AM
Data Validation, Custom leimst Excel Worksheet Functions 1 June 13th 08 12:24 AM
Data Validation, Custom leimst Excel Worksheet Functions 1 June 13th 08 12:23 AM
Combining conditions for data entry validation Richard H Knoff Excel Worksheet Functions 10 November 14th 04 02:49 PM


All times are GMT +1. The time now is 07:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"