Data Validation: two conditions

J

John McGhie

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

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


John McGhie said:
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?
 
J

John McGhie

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


papou said:
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 said:
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?
 
M

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

John McGhie

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
 
A

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

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

John McGhie

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
 
P

papou

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

John McGhie said:
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...
 
L

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.

John McGhie said:
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?
 
J

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

Leo Heuser

Hi Pascal
..

papou said:
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.
 
L

Leo Heuser

Hi JE

JE McGimpsey said:
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.
 
J

John McGhie

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


Leo Heuser said:
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.

John McGhie said:
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?
 
L

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 McGhie said:
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
:)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top