unique cell entry

[Guest]

I have a worksheet that several people enter info onto my problem is that
some of the info gets duplicated and causes a miscount in the numbers, I have
to go back and track down the duplicate entries and delete them. I have
column headers like this
TICKET# PRODUCT TANK# LOADER STARTTIME

and several other heading. The ticket# is a unique non duplicating, is there
anyway I can get the workbook to not accept a TICKET# that has already been
entered in previously in the worksheet.

 

[Bob Phillips]

Trap it on input by adding data validation to those cells with a formula
like

=COUNTIF($A$1:A1,A1)=1

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob that works fine and I am able to notice the entry duplicate by the
background change but our tickets are in a desending order 8 characters long.
Is there anyway that once the number is entered that if it matches a
previously entered number that it won't accept it or notify the person
entering the info that this number has already been entered.

 

[Bob Phillips]

I said data validation (Data>Validation), not conditional formatting. Use a
type of custom with that formula and you will see an error on input.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob I was putting the formula in my validation, I guess I was just entering
it wrong after I re-entered it being carful to be exact as you wrote the
formula worked fine the problem was all with me. THANK YOU for your quick
responce and helpful formula.

 

[Sinner]

There is one problem Bob.
When we paste a value that is already in the list, the validation
doesn't work unless ofcourse press F2 and hit enter) making it work as
if you entered the value in cell - It's only then the validation
works.
Is there a work around to catch duplicate entry even if the value is
pasted in same list & not entered?

The mentioned formula works fine while value is "entered" & not when
"pasted" : )

--------------------------------------------------------------------------------------------------------------------------------------------

 

[Bob Phillips]

No, you would have to use VBA to do that.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sinner]

No, you would have to use VBA to do that.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

Dear Bob,

Can you code that for better understanding.

Thx

 

[Guest]

Bob I thought I had the perfect way to keep the cell entry in our ticket #
cells from being duplicated but now they are coping and pasteing rows instead
of entering all of the information and we are now getting duplicate numbers
and it is throwing all of our counts for the month off. You mentioned that
duplicates could be trapped by a VBA, could you suggest one or a place I can
find it.