A formula can not do waht you ask, it can however show you if your entry
is a duplicate, to simply have a formula look at the cell you make an
entry in you could use this =IF(COUNTIF(A1:A100,B1)=1,"Duplicate",B1)
enter your number in B1 and the formula should say Duplicate or show
your B1 value, you can use it in conjunction with conditional
formatting, Chip Pearson explains it very well here 'Duplicates In Data
In Excel' (http://www.cpearson.com/excel/Duplicates.aspx)

Bryan;574074 Wrote:
Hello all.

I have a team of 15 pople that all work with reference numbers, on
occasion
a number has to be sent to a different work group. I've created a
spreadsheet
for them to enter the numbers into, but we have been duplicating
numbers. I
want to create a field to enter th number into, have the field cross
reference the exsting numbers, and if it does not find a match, enter
the
number and sort the list.

I know how to create a custom macro that will accomplish what I want to
do,
but I'd prefer a static formula if it's possible.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158623

Microsoft Office Help