| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula which checks multiple criteria before counting | Excel Discussion (Misc queries) | |||
| multiple choice data validation | Excel Discussion (Misc queries) | |||
| Multiple Data Validation | Excel Discussion (Misc queries) | |||
| How to do multiple IF checks with dates AND text | Excel Worksheet Functions | |||
| Data validation for Multiple columns | Excel Discussion (Misc queries) | |||
Hybrid Mode
