Multiple Data Validation Checks

  • Thread starter Thread starter FARAZ QURESHI
  • Start date Start date
F

FARAZ QURESHI

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!
 
Presume there was a typo here in this spec line:
.. except for some 4-digit numbers listed in a range named "WRONG"!
Click to expand...

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

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(MATCH(A1,WRONG,0)))

---
 
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(ISNUMBER(MATCH(A1,WRONG,0))))

Hopefully this is the correct one?
 
=AND(AND(ISNUMBER(A1),A1>=1000,A1<=9999),NOT(ISNUMBER(MATCH(A1,WRONG,0))))
Hopefully this is the correct one?
Click to expand...

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(MATCH(A1,WRONG,0)))

---
 
Back
Top