Re: Data Validation: two conditions

From: anonymous (anonymous_at_discussions.microsoft.com)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 00:15:14 -0700

Doesn't work!

Problem with the definition of the last 3 numeric
characters. The op wants the last 3 characters to be ###.

Your formula will accept # or ########.

>-----Original Message-----
>Hi John
>Here's a try:
>(rngValue is a name defined for your list of values)
>
>=AND(OR(NOT(ISERROR(MATCH(LEFT(A1,2),RngValues,0))),NOT
(ISERROR(MATCH(LEFT(A
>1,3),RngValues,0)))),OR(VALUE(RIGHT(A1,3))>=1,VALUE(RIGHT
(A1,3))<=999))
>HTH
>Cordially
>Pascal
>
>
>"John McGhie" <john - at - mcghie.name> a écrit dans le
message de
>news:752A2EBE-49A9-437B-A07E-1C37418CFB61@microsoft.com...
>> Hi All:
>>
>> Can someone please save me having to think? ...
>>
>> I need to validate entries in a column of cells which
must take the format
>of xxx999 where xxx must be one of the following two or
three character
>values: QLD, NSW, VIC, TAS, SA, WA, NT; and 999 must be a
value between 001
>and 999.
>>
>> I'm struggling to understand what formula should go in
the Data Validation
>custom dialogue box.
>>
>> Can I do this with data validation? If so, what is the
formula I use to
>validate the two components? The state abbreviation is
easy: that's just a
>named list, right?
>>
>> NSW
>> NT
>> QLD
>> SA
>> TAS
>> VIC
>> WA
>>
>> It's validating that the concatenated digits to the
right are between 001
>and 999 that has me stumped.
>>
>> Please advise...
>>
>> --
>> John McGhie <john@mcghie.name>
>> MVP Microsoft Word and Word for Mac
>
>
>.
>