Preventing Duplicate Entries Across Cells

[Ken D.]

Anyone know how to prevent duplicate entries of numbers.
e.g. I have two sheets. I want to make sure that the same
number is not entered more than once. Thank you.

 

[DNF Karran]

I think this might work-

Have a countif funtion (of pair of for 2 sheets) that look a th
current row and see if it has duplicate entries ie

a b
1 1 =COUNTIF($A$1:$A$2,A1) - 1
2 2 =COUNTIF($A$1:$A$2,A2) - 1

(the minus one has been added so when a number exists only once it wil
return 0. An if statement may also be need for if hte cell is blank.)

Then add this behind the worksheet:

Private Sub Worksheet_Calculate()
If Application.WorksheetFunction.Sum("B:B") <> 0 Then
MsgBox "Duplicate Entry Has Been Made"
End If
End Sub

This method will need a little tweaking and prevention may be a littl
more difficult. Tools/Data Validation may be worth a try....

Hope this helps

Dunca

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/NoDupEntry.htm

 

[Ken]

Unfortunately, the site listed does not explain how to
check one sheet for entry on a different sheet. That's
what i'm having trouble with.

 

[Frank Kabel]

Hi
easy to change:
- Define a name for the range on your second sheet (goto 'Insert -
Name - Define'). lets say you call this range 'test_range'
- now change the formula found on the website to
=COUNTIF(test_range,A1)=0
to prevent an entry on the sheet which is already in the test_range

Repeat this for the other sheet