Data Validation

[k1ngr]

How can I do Data Entry Validation for all the cells in a column.

I want to make sure all entries are:
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

 

[OssieMac]

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)

 

[k1ngr]

It worked great.

Thanks,
Dick

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

COUNTIF(A:A,A1)=1 (ensures only one entry in the column)