Hi Dana,

Try to apply this formula in Validation/Specific

=AND(OR(LOWER(LEFT(A1,1))="a",LOWER(LEFT(A1,1))="c ",LOWER(LEFT(A1,1))="e",LOWER(LEFT(A1,1))="f") , LEN(A1)=7, ISNUMBER(VALUE(MID(A1,2,6))))

Regards,
Stefi

€žDana€� ezt Ã*rta:

Im creating a spreadsheet for data collection from various employees and am
trying to make it as €œgoof-proof€� as possible.

The first column must not be blank, must be unique, and requires one (1)
lower case text character (currently a, c, e, or f) followed by any six (6)
numbers. i.e. e123456. This will be used to validate against a master
database later.

I have a conditional format set to show if the cell is duplicated using
=COUNTIF($A$2:$A$1000,A2)1. The format for this condition changes the color
of both duplicate cells to red so they can correct it.

I have validation set to require exactly seven (7) characters. The €œignore
blank€� checkbox is unchecked. The input and stop messages gives them an
example.

This still allows any entry that is seven characters, numeric or text (upper
or lower case) or any combination.

What Id like to do is test their entry for our exact requirements and if
possible, stop them from moving on until it is correct. Can this be done
using worksheet functions or does it require a VBA script?

I reallize this would probably be easier in MS Access, but not all have the
program and many wouldn't be able to use it.

TIA.

Dana Scott