Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
How can I do Data Entry Validation for all the cells in a column.
I want to make sure all entries a 1. Exactly two characters long. 2. Are all upper case. 3. Are only alpha-characters. 4. Are not duplicated (no two cells contain the same sequence of two characters). Dick King |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Hi Dick,
Select the column that you want to be validated. Select Data-Validation (or Data-Data Validation if xl2007) On Settings tab under Allow: Select custom check the allow blank box. Copy the following formula into the Formula box. =AND(LEN(A1)=2,CODE(LEFT(A1,1))=65,CODE(LEFT(A1,1 ))<=90,CODE(RIGHT(A1,1))=65,CODE(RIGHT(A1,1))<=90 ,COUNTIF(A:A,A1)=1) Note that the formula is one continuous line but will most likely be broken with a line feed on this forum. You might have to copy it into Notepad and remove the line feed. (I have not been able to edit them in the validation formula box.) Also if your column is not column A then all the A1's plus A:A in countif will have to be changed to your column. Explanation:- LEN(A1)=2 (ensures 2 characters long) CODE(LEFT(A1,1))=65 and CODE(LEFT(A1,1))<=90 (ensures left character is = uppercase A and <= to uppercase Z) COUNTIF(A:A,A1)=1 (ensures only one entry in the column) -- Regards, OssieMac "k1ngr" wrote: How can I do Data Entry Validation for all the cells in a column. I want to make sure all entries a 1. Exactly two characters long. 2. Are all upper case. 3. Are only alpha-characters. 4. Are not duplicated (no two cells contain the same sequence of two characters). Dick King |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
It worked great.
Thanks, Dick "OssieMac" wrote: Hi Dick, Select the column that you want to be validated. Select Data-Validation (or Data-Data Validation if xl2007) On Settings tab under Allow: Select custom check the allow blank box. Copy the following formula into the Formula box. =AND(LEN(A1)=2,CODE(LEFT(A1,1))=65,CODE(LEFT(A1,1 ))<=90,CODE(RIGHT(A1,1))=65,CODE(RIGHT(A1,1))<=90 ,COUNTIF(A:A,A1)=1) Note that the formula is one continuous line but will most likely be broken with a line feed on this forum. You might have to copy it into Notepad and remove the line feed. (I have not been able to edit them in the validation formula box.) Also if your column is not column A then all the A1's plus A:A in countif will have to be changed to your column. Explanation:- LEN(A1)=2 (ensures 2 characters long) CODE(LEFT(A1,1))=65 and CODE(LEFT(A1,1))<=90 (ensures left character is = uppercase A and <= to uppercase Z) COUNTIF(A:A,A1)=1 (ensures only one entry in the column) -- Regards, OssieMac "k1ngr" wrote: How can I do Data Entry Validation for all the cells in a column. I want to make sure all entries a 1. Exactly two characters long. 2. Are all upper case. 3. Are only alpha-characters. 4. Are not duplicated (no two cells contain the same sequence of two characters). Dick King |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Data Validation Update Validation Selection | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |