Thanks very much indeed both of you - I have managed to get my sheet to stop
invalid entries and show duplicates. I've used Ashish' last formula in data
validation and added a column with if formula to show duplicate entries. Biff
I just could not get your formula to work in data validation no matter how I
enter it - when I highlighted the whole range to enter the data validation it
chnaged D2 to D64529 in all cells so not a clue what is going on!

Anyway I have managed to get my workbook to do what i want so thanks!

P.S. - sorry for delay responding to your assistance but been away all week!


"T. Valko" wrote:

See the formula I suggested to the OP. There's also a link to a sample file
for demonstration.

The formula is "ugly" even by my standards!

I don't know how to limit the length of the number portion since it varies
from 3 digits to 6 digits so about the only thing I could think of was to
test for an allowable min and max string length.

ABC 123 = 7 characters
ABC 1234 = 8 characters
ABC 12345 = 9 characters
ABC 123456 = 10 characters

So the entry must be at least 7 characters but not more than 10 characters.

The OP seems to be having trouble implementing this in their application,
though.

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

Thank you for pointing this out. This modification takes care of problem
1 and 2. Problem 3 still persists - infact if I put a test for the
numeric portion being between 3 and 6 digits, the formula becomes long
enough not to be accepted in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))<=90,C ODE(MID(C13,2,1))=65,CODE(MID(C13,2,1))<=90,CODE( MID(C13,3,1))=65,CODE(MID(C13,3,1))<=90,CODE(MID( C13,4,1))=32,mod(1*(RIGHT(C13,LEN(C13)-SEARCH("
",C13))),1)=0)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"T. Valko" wrote in message
...
That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote:

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))<=90,C ODE(MID(C13,2,1))=65,CODE(MID(C13,2,1))<=90,CODE( MID(C13,3,1))=65,CODE(MID(C13,3,1))<=90,CODE(MID( C13,4,1))=32,ISNUMBER(1*(RIGHT(C13,LEN(C13)-SEARCH("
",C13)))))

This will allow a user entry where the first three characters are
capitalised alphabets, fourth character is a space and after the space
there
is a number

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
I have a sheet where I have a reference number in Col D that is
comprised
of
3 letters, a space then 3, 4, 5 or 6 digits.. I need to identify
duplicate
entries as people enter a duplicate reference number

Col D
ABC 1234
ABS 22221
ABR 124
ASR 554477

I have managed to find a formula within data validation that will give
an
error message if a duplicate reference is entered however I need to
add
something to make sure that only three letters , then a space, then
numbers
can be input or that peoiple can only input letters and numbers with
no
spaces or other characters.

The formula in my data validation is =COUNTIF(D$2:D$4999,D2)<=1

Any suggestions very welcome - most entries are being input correctly
however a few are including a / between text and numbers instead of
space
or
are adding a space after numbers so my current formula is not picking
up
these as duplicates.

I'm using Excel 2000

Lilyput




.