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

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

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