Allow any Certain chr within a cell

T

Trever B

Hi

My question is in two parts:-

1. How do I restrict the contents of a cell a single chr namely "A to F"
or 1-9"

2. Since the cell is only allowed to be 1 digit long when I have keyed
and checked it moves on to the next cell automatically


Thanks in advance

Trev
 
D

David McRitchie

Hi Trever,

Use cell validation: -- Part 1 of question

Data (menu), validation

allow: Text length
Data: between
Minimum: 1
Maximum 1
[ x] ignore blanks

After you have tested with the above to see the error produced, would
refine the error message by continuing with "Input Message" (tab)
within your validation

[_ ] Show input message when cell is select
(leave Show input message empty, as the message is too distracting, too close to input area)
When cell is selected show this input message:
title: Input restriction on length
Input Message: Input for this cell is limited to 1 character, please reenter.

A refinement on the above for strictly letter or number
Custom: (which means entering a formula based on the active cell)
formula is: =OR(AND(B30>="A",B30<="Z"), AND(B30>=0,B30<=9))

Warning:
You will have trouble with this formula if you format the cell as text
In other words letters will be treated as text, and digits as numbers.

If someone pastes into the cell then validation testing will be bypassed, but
the validation test will remain active for the next time if properly entered.


Part 2: automatically move to the next cell

You must hit Enter, Tab,
or in other situations you may alternatively use the Arrow Key
to enter the data, Until you do so, Excel does not recognize
what you are entering -- Excel does not recognize keystroke entry..
 
D

Dave Peterson

I think the closest thing you can do using built in tools is:
Data|Validation

Use a list (either on the worksheet or typed in directly in that dialog)

But this won't fire until the user hits enter--So they can type whatever they
want for as long as they want. They will get a warning if it's not a valid
entry, though.

You could set an option to move in a certain direction when you hit enter, too:

Tools|Options|edit tab|Move selection after enter

But this is a user setting--not a workbook setting. Each user has to change
this themselves.
 
Top