Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Two questions:
1st data validation issue: I don't want the data entry people to have to stop typing and pick up the mouse to click from a drop down list so how can I make a cell accept either Y, y, N, or n using the custom formula? 2nd validation issue: I am requiring that a cell contain the first letter M followed by 8 digits and 8 digits only. I've got the validation mask below and although it works for allowing only an M and numbers, it allows the user to enter as many numbers as they want whereas I only want to allow 8. How can I also restrict it to being 8 numbers after the M and only 8 numbers? =AND(LEFT(A6,1)="m",RIGHT(A6,8)*10,RIGHT(A6,8)*1< =99999999) Any assistance that anyone could provide would be very much appreciated. Sam |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
How about just checking the length...
=AND(LEFT(A6,1)="m",RIGHT(A6,8)*10,RIGHT(A6,8)*1< =99999999, len(A6) = 9) -- HTH... Jim Thomlinson "SFoley" wrote: Two questions: 1st data validation issue: I don't want the data entry people to have to stop typing and pick up the mouse to click from a drop down list so how can I make a cell accept either Y, y, N, or n using the custom formula? 2nd validation issue: I am requiring that a cell contain the first letter M followed by 8 digits and 8 digits only. I've got the validation mask below and although it works for allowing only an M and numbers, it allows the user to enter as many numbers as they want whereas I only want to allow 8. How can I also restrict it to being 8 numbers after the M and only 8 numbers? =AND(LEFT(A6,1)="m",RIGHT(A6,8)*10,RIGHT(A6,8)*1< =99999999) Any assistance that anyone could provide would be very much appreciated. Sam |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
You are brilliant. Thanks so much. Worked perfectly and I figured out the
Y,y,N,n issue on my own so I'm good to go now. Thanks again! "Jim Thomlinson" wrote: How about just checking the length... =AND(LEFT(A6,1)="m",RIGHT(A6,8)*10,RIGHT(A6,8)*1< =99999999, len(A6) = 9) -- HTH... Jim Thomlinson "SFoley" wrote: Two questions: 1st data validation issue: I don't want the data entry people to have to stop typing and pick up the mouse to click from a drop down list so how can I make a cell accept either Y, y, N, or n using the custom formula? 2nd validation issue: I am requiring that a cell contain the first letter M followed by 8 digits and 8 digits only. I've got the validation mask below and although it works for allowing only an M and numbers, it allows the user to enter as many numbers as they want whereas I only want to allow 8. How can I also restrict it to being 8 numbers after the M and only 8 numbers? =AND(LEFT(A6,1)="m",RIGHT(A6,8)*10,RIGHT(A6,8)*1< =99999999) Any assistance that anyone could provide would be very much appreciated. Sam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation | Excel Worksheet Functions | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | 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 |