| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
I require a Data Validation that will only allow up to 6 digits to be
inserted. This can only allow digits 1 - 9 as any one of the 6 digits. I've tried Text Length, but that allows characters - / T etc Any ideas? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation
Try this:
DataValidation Allow: Whole Number Between 111111 and 999999 Does that do what you want? *********** Regards, Ron "John" wrote: I require a Data Validation that will only allow up to 6 digits to be inserted. This can only allow digits 1 - 9 as any one of the 6 digits. I've tried Text Length, but that allows characters - / T etc Any ideas? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation
,If you need 6 digits you data validation allow: whole number data: between
minimum: 100,000 maximum: 999999. If less than 6 digits use less than 1,000,000 HTH Sally "John" wrote: I require a Data Validation that will only allow up to 6 digits to be inserted. This can only allow digits 1 - 9 as any one of the 6 digits. I've tried Text Length, but that allows characters - / T etc Any ideas? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
My prev doesn't work...maybe this
Set the Number format to TEXT
Set Data Validation to: Category: Custom Formula: =AND(ISNUMBER(N(A1)),LEN(A1)<=6,ISERROR(FIND("0",A 1)),ISERROR(FIND(".",A1)),ISERROR(FIND("-",A1)),ISERROR(FIND("+",A1)),ISERROR(FIND("=",A1)) ) So far, that checks if: the input is numeric The length is 6 or less There are no zeros, decimal points, plus or minus signs, or equal signs. Just tack on anything else that violates your rules. I hope that helps? *********** Regards, Ron "Ron Coderre" wrote: Try this: DataValidation Allow: Whole Number Between 111111 and 999999 Does that do what you want? *********** Regards, Ron "John" wrote: I require a Data Validation that will only allow up to 6 digits to be inserted. This can only allow digits 1 - 9 as any one of the 6 digits. I've tried Text Length, but that allows characters - / T etc Any ideas? Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
more tweaking
The Data Validation formula should be:
=AND(ISNUMBER(--A1),LEN(A1)<=6,ISERROR(FIND("0",A1)),ISERROR(FIND( ".",A1)),ISERROR(FIND("-",A1)),ISERROR(FIND("+",A1)),ISERROR(FIND("=",A1)) ) Changed ISNUMBER(N(A1)) to ISNUMBER(--A1). ( ISNUMBER(N(A1)) allowed text ) After that, I'm out of ideas? I hope that helps. *********** Regards, Ron "Ron Coderre" wrote: Set the Number format to TEXT Set Data Validation to: Category: Custom Formula: =AND(ISNUMBER(N(A1)),LEN(A1)<=6,ISERROR(FIND("0",A 1)),ISERROR(FIND(".",A1)),ISERROR(FIND("-",A1)),ISERROR(FIND("+",A1)),ISERROR(FIND("=",A1)) ) So far, that checks if: the input is numeric The length is 6 or less There are no zeros, decimal points, plus or minus signs, or equal signs. Just tack on anything else that violates your rules. I hope that helps? *********** Regards, Ron "Ron Coderre" wrote: Try this: DataValidation Allow: Whole Number Between 111111 and 999999 Does that do what you want? *********** Regards, Ron "John" wrote: I require a Data Validation that will only allow up to 6 digits to be inserted. This can only allow digits 1 - 9 as any one of the 6 digits. I've tried Text Length, but that allows characters - / T etc Any ideas? Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation
Thanks guys
Using Whole numbers between 1 - 999999 does the trick "Sally" wrote in message ... ,If you need 6 digits you data validation allow: whole number data: between minimum: 100,000 maximum: 999999. If less than 6 digits use less than 1,000,000 HTH Sally "John" wrote: I require a Data Validation that will only allow up to 6 digits to be inserted. This can only allow digits 1 - 9 as any one of the 6 digits. I've tried Text Length, but that allows characters - / T etc Any ideas? Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
more tweaking
Thanks Ron, you put a good deal of effort into your reply
"Ron Coderre" wrote in message ... The Data Validation formula should be: =AND(ISNUMBER(--A1),LEN(A1)<=6,ISERROR(FIND("0",A1)),ISERROR(FIND( ".",A1)),ISERROR(FIND("-",A1)),ISERROR(FIND("+",A1)),ISERROR(FIND("=",A1)) ) Changed ISNUMBER(N(A1)) to ISNUMBER(--A1). ( ISNUMBER(N(A1)) allowed text ) After that, I'm out of ideas? I hope that helps. *********** Regards, Ron "Ron Coderre" wrote: Set the Number format to TEXT Set Data Validation to: Category: Custom Formula: =AND(ISNUMBER(N(A1)),LEN(A1)<=6,ISERROR(FIND("0",A 1)),ISERROR(FIND(".",A1)),ISERROR(FIND("-",A1)),ISERROR(FIND("+",A1)),ISERROR(FIND("=",A1)) ) So far, that checks if: the input is numeric The length is 6 or less There are no zeros, decimal points, plus or minus signs, or equal signs. Just tack on anything else that violates your rules. I hope that helps? *********** Regards, Ron "Ron Coderre" wrote: Try this: DataValidation Allow: Whole Number Between 111111 and 999999 Does that do what you want? *********** Regards, Ron "John" wrote: I require a Data Validation that will only allow up to 6 digits to be inserted. This can only allow digits 1 - 9 as any one of the 6 digits. I've tried Text Length, but that allows characters - / T etc Any ideas? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| Data Validation | Excel Worksheet Functions | |||
| data validation lists | Excel Discussion (Misc queries) | |||
| named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Linear Mode
