Data Validation with multiple criteria

**hedeyatch** · 09-11-2009, 12:17 AM

Originally Posted by ncmay

You could try the websites below:

http://www.ozgrid.com/forum/showthread.php?t=94312

http://www.contextures.com/xlDataVal02.html

Hi all,
I have data :

A B
C0360000B038 = ???
C036000B8
C036000B10

Every last character B038 must consist of 12 digits, if last character B8 must consist of 9 digits, and if last character B10 must consist of 10 digits.

if I have only one conditions I can fill validation data with :
=LEN(A1)=12
Error Alert :
"Must Be 12 digits"

but its only for one condition how about 3.

**Paul** · 09-11-2009, 12:25 AM

Hi Hedeyatch,

When I asked you to read the forum rules via PM, I wasn't really asking. I've removed this post from the end of the thread you replied to and created this new thread.

Please (re-)read the forum rules and pay close attention to rules 2 and 4.

Thanks.

**DonkeyOte** · 09-11-2009, 02:00 AM

Perhaps Custom Validation rule of:

=LEN(A1)=IF(RIGHT(A1,4)="B038",12,IF(RIGHT(A1,3)="B10",10,IF(RIGHT(A1,2)="B8",9,0)))

where A1 is the cell against which the validation is applied and cell subsequently copied as required
note: the above would restrict any not B8,B10,B038 entries