Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Allow any Certain chr within a cell
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 |
#2
|
|||
|
|||
Allow any Certain chr within a cell
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.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Trever B" wrote 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 |
#3
|
|||
|
|||
Allow any Certain chr within a cell
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. Trever B wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
Cell Change Color - Need Help | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |