Data Validation: two conditions

[Guest]

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...

 

[papou]

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

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?

 

[Guest]

Hi Papou:

Many thanks: Looks like a winner. It will be a minute or two before I get to try that one out, so here's hoping

I really appreciate your time.

Cheers
--
John McGhie <[email protected]>
MVP Microsoft Word and Word for Mac

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

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?

 

[Mark]

Gidday (from Melbourne),

Data Validation is only used if a number entered into the
range does not meet criteria set by you.

Also your range of numbers 1 to 999 does not leave any
room for anything else unless the number is less than 0 or
more than 1000.

In that case,
Allow =Whole Number
Minimum=1
Maximum=999

When the user inputs a number say, 1.22 an alert comes up.
if the number is negative or 1000+, also alert.

Does this help?
regards
Mark
http://au.geocities.com/excelmarksway

-----Original Message-----
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.

 

[Guest]

Hi Mark:

I am not sure I understand you. If I set the validation to "Whole Number", how am I going to accept the State abbreviation at the front of the string?

Enquiring minds (in Sydney) wish to know

Cheers

 

[anonymous]

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 ########.

 

[papou]

Well?
As regards John's initial requirements it seems that this works, tested on
my Excel 2003.

Cordially
Pascal

"anonymous" <[email protected]> a écrit dans le message de
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 ########.

 

[Guest]

Errrpppsss... You are right In Excel 2003, that formula will not compile, let alone work I am trying to find the error as we speak...

Cheers

 

[papou]

John
If required, I can send an example workbook that works with me!
Cordially
Pascal

Errrpppsss... You are right In Excel 2003, that formula will not

compile, let alone work I am trying to find the error as we speak...

 

[Leo Heuser]

Hi John

This formula should do the trick:

=OR(A1=PrefixList&TEXT(TRANSPOSE(ROW(INDIRECT("1:999"))),"000"))

PrefixList is the list of, well, prefixes

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

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?

 

[JE McGimpsey]

Hi John!

One way:

Put your state abbreviations (2 or 3 characters) in a column of cells
and name the range "Letters". Then use this for your Validation formula:

=AND(NOT(ISNA((MATCH(LEFT(A1,LEN(A1)-3),Letters,FALSE)))), OR(LEN(A1)=5,
LEN(A1)=6), ISNUMBER(--RIGHT(A1,3)))

Letters can be on a separate sheet, which you can hide, if desired.

 

[Leo Heuser]

Hi Pascal
..

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

Unfortunately your formula will allow 4 or
more digits to the right, since you are only
testing the rightmost three digits.

 

[JE McGimpsey]

Ah, forget this - use Leo's.

 

[Leo Heuser]

Hi JE

Hi John!

One way:

Put your state abbreviations (2 or 3 characters) in a column of cells
and name the range "Letters". Then use this for your Validation formula:

=AND(NOT(ISNA((MATCH(LEFT(A1,LEN(A1)-3),Letters,FALSE)))), OR(LEN(A1)=5,
LEN(A1)=6), ISNUMBER(--RIGHT(A1,3)))

Letters can be on a separate sheet, which you can hide, if desired.

Your formula will allow entries like VIC000 VIC,67 VIC+08 VIC-98 etc.

 

[papou]

Ok got it - been a bit slow on that one ;-)
Cordially
Pascal

 

[Guest]

John/Leo:

I am DEEPLY grateful to you both This is for my housemate/former boss/current customer. I am trying to get a manual on the press for a different customer right now, and I simply did not have time to work this up step by step.

You have really saved me on this one. I am not sure that it's "nice" to be reminded of how desperate our original posters are when they post, but it sure is a useful reminder of why we do this; when the tables are turned and all of a sudden it's ME that needs the answer by close of business yesterday

Cheers all
--
John McGhie <[email protected]>
MVP Microsoft Word and Word for Mac

Hi John

This formula should do the trick:

=OR(A1=PrefixList&TEXT(TRANSPOSE(ROW(INDIRECT("1:999"))),"000"))

PrefixList is the list of, well, prefixes

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

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?

 

[Leo Heuser]

Glad I could help you, John.
Yes, we can all get pretty desperate, and then it's good
to know, that help is available "out there"
I value your feedback!

LeoH
MVP Excel

John/Leo:

I am DEEPLY grateful to you both This is for my housemate/former

boss/current customer. I am trying to get a manual on the press for a
different customer right now, and I simply did not have time to work this up
step by step.

You have really saved me on this one. I am not sure that it's "nice" to

be reminded of how desperate our original posters are when they post, but it
sure is a useful reminder of why we do this; when the tables are turned and
all of a sudden it's ME that needs the answer by close of business yesterday

 

[JE McGimpsey]

Your formula will allow entries like VIC000 VIC,67 VIC+08 VIC-98 etc.

Yup - s'one reason I told John to use yours...