Data Validation: two conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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...
 
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
Click to expand...
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
Click to expand...
validate the two components? The state abbreviation is easy: that's just a
named list, right?
 
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
Click to expand...
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
Click to expand...
validate the two components? The state abbreviation is easy: that's just a
named list, right?
Click to expand...
 
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
Click to expand...
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
Click to expand...
the Data Validation custom dialogue box.
Can I do this with data validation? If so, what is the
Click to expand...
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
Click to expand...
are between 001 and 999 that has me stumped.
 
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
 
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 ########.
 
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 ########.
 
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
 
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
Click to expand...
compile, let alone work :-) I am trying to find the error as we speak...
 
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
Click to expand...
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
Click to expand...
validate the two components? The state abbreviation is easy: that's just a
named list, right?
 
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.
 
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
Click to expand...

Unfortunately your formula will allow 4 or
more digits to the right, since you are only
testing the rightmost three digits.
 
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.
Click to expand...

Your formula will allow entries like VIC000 VIC,67 VIC+08 VIC-98 etc.
 
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
Click to expand...
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
Click to expand...
validate the two components? The state abbreviation is easy: that's just a
named list, right?
Click to expand...
 
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
Click to expand...
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
Click to expand...
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
:-)
 
Back
Top