Multiple Data Validation Checks
 

I want to restrict a user for input of numbers only between 1000 to 9999
except for some 4-digit numbers listed in a range named "WRONG"!

Any idea how to put a Data Validation check by custom or any other option?

Thanx!

Multiple Data Validation Checks
 

Presume there was a typo here in this spec line:
.. except for some 4-digit numbers listed in a range named "WRONG"!

and that you actually meant, say:
.. except for some 3-digit numbers listed in a range named "WRONG"!

since your main specs covered allowing all 4 digits between 1000 - 9999 ??

If so, try Data Validation, Allow: Custom,
Formula:
=OR(AND(ISNUMBER(A1),A1=1000,A1<=9999),ISNUMBER(M ATCH(A1,WRONG,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
I want to restrict a user for input of numbers only between 1000 to 9999
except for some 4-digit numbers listed in a range named "WRONG"!

Any idea how to put a Data Validation check by custom or any other option?

Thanx!

Multiple Data Validation Checks
 

Sorry Max,

But what I meant was that I want the user to be entering numbers from 1000
to 9999 except for 1111, 2222, 3333, 4444, 5555, 6666, 7777, 8888, 9999
listed in the range!

However, your formula surely did help out and I have modified it to:

=AND(AND(ISNUMBER(A1),A1=1000,A1<=9999),NOT(ISNUM BER(MATCH(A1,WRONG,0))))

Hopefully this is the correct one?

"Max" wrote:

Presume there was a typo here in this spec line:
.. except for some 4-digit numbers listed in a range named "WRONG"!

and that you actually meant, say:
.. except for some 3-digit numbers listed in a range named "WRONG"!

since your main specs covered allowing all 4 digits between 1000 - 9999 ??

If so, try Data Validation, Allow: Custom,
Formula:
=OR(AND(ISNUMBER(A1),A1=1000,A1<=9999),ISNUMBER(M ATCH(A1,WRONG,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
I want to restrict a user for input of numbers only between 1000 to 9999
except for some 4-digit numbers listed in a range named "WRONG"!

Any idea how to put a Data Validation check by custom or any other option?

Thanx!

Multiple Data Validation Checks
 

=AND(AND(ISNUMBER(A1),A1=1000,A1<=9999),NOT(ISNUM BER(MATCH(A1,WRONG,0))))

Hopefully this is the correct one?

Yes, as I'm sure you would have tested it out yourself, right?

You could replace: NOT(ISNUMBER(MATCH(A1,WRONG,0)))
with the slightly shorter: ISERROR(MATCH(A1,WRONG,0))

ie this expression should work just as well:
=AND(AND(ISNUMBER(A1),A1=1000,A1<=9999),ISERROR(M ATCH(A1,WRONG,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote in message
...
Sorry Max,

But what I meant was that I want the user to be entering numbers from 1000
to 9999 except for 1111, 2222, 3333, 4444, 5555, 6666, 7777, 8888, 9999
listed in the range!

However, your formula surely did help out and I have modified it to:

=AND(AND(ISNUMBER(A1),A1=1000,A1<=9999),NOT(ISNUM BER(MATCH(A1,WRONG,0))))

Hopefully this is the correct one?