How do I prevent duplicate entries across sheets

[kmdavidson]

I have searched and searched but haven't come up with an answer to this:

Does anyone know how to prevent duplicate entries when using more than one sheet. e.g. I want to set it up so that I can only use a number once. So if the number was already entered on sheet 2 and I try to enter it on sheet 1, I get an error message. I know it has to do with data validation and defining a range, but I can't get the syntax and instructions right. Thanks.

I have been using Microsoft's Knowledge Base Article 213185 as a model.

 

[DRJ]

Hi - Welcome to the board

You cannot reference other worksheets with data validation, only the worksheet with the data validation can be used. You could group all the data onto each sheet. In an unused column just grab all the data from the other sheets and put it into one column. Then in validation use countif to make sure there is only one entry.

 

[erik.van.geit]

what ranges has to be checked: certain cells on certain sheets or all cells on all sheets ?
how many sheets are there ?

regards,
Erik

 

[kmdavidson]

There are two sheets and once column per sheet for the numbers (but many columns used for other information)

 

[DRJ]

How many rows are used in the columns?

 

[kmdavidson]

I need a formula that will permit hundreds of rows because rows will be added over time.

 

[DRJ]

You can use a vba worksheet_change event to handle this instead on validation.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.Column = 1 Then Exit Sub 'Only work on column A

If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), Target.Value) + Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), Target.Value) > 1 Then
    MsgBox "You have entered a duplicate number, please try again"
    Target.ClearContents
    Else
End If

End Sub

 

[kmdavidson]

Thank you very much for the help and quick replies. This seems to work alright. But the only problem is that it no longer allows cut and past functionality - e.g. if i want to cut row2, sheet 1 and paste into sheet 2. Any ideas?

 

[DRJ]

try this instead

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.Column = 1 Then Exit Sub 'Only work on column A

If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), Sheets("Sheet1").Range("A" & Target.Row).Value) + Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), Sheets("Sheet2").Range("A" & Target.Row).Value) > 1 Then
    MsgBox "You have entered a duplicate number, please try again"
    Target.ClearContents
    Else
End If

End Sub

 

[Joe Was]

When you get it to work the way you want you may wany to add a MsgBox that tells you what sheet the Dup is on, something like this using DJR code?

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet1 code!

Dim mySheet As String

If Not Target.Column = 1 Then Exit Sub 'Only work on column A

'Same sheet dup!
If Application.Selection.Value = Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), Target.Value) Then
mySheet = "This value is already entered on ""Sheet1 Column A!"""
Else
'Other sheet dup.
mySheet = "This value is already entered on ""Sheet2 Column A!"""
End If

If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), Target.Value) + Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), Target.Value) > 1 Then
MsgBox mySheet
Target.ClearContents
Else
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet2 code!

Dim mySheet As String

If Not Target.Column = 1 Then Exit Sub 'Only work on column A

'Same sheet dup!
If Application.Selection.Value = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), Target.Value) Then
mySheet = "This value is already entered on ""Sheet2 Column A!"""
Else
'Other sheet dup!
mySheet = "This value is already entered on ""Sheet1 Column A!"""
End If

If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), Target.Value) + Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), Target.Value) > 1 Then
MsgBox mySheet
Target.ClearContents
Else
End If

End Sub