Multiple Data Validation Checks

[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!

 

[Max]

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

---

 

[FARAZ QURESHI]

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?

 

[Max]

=AND(AND(ISNUMBER(A1),A1>=1000,A1<=9999),NOT(ISNUMBER(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(MATCH(A1,WRONG,0)))

---