How do I prevent duplicate entries across sheets

kmdavidson

New Member
Joined
Apr 15, 2004
Messages
4
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Apr 15, 2004
Messages
4
There are two sheets and once column per sheet for the numbers (but many columns used for other information)
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853

ADVERTISEMENT

How many rows are used in the columns?
 

kmdavidson

New Member
Joined
Apr 15, 2004
Messages
4
I need a formula that will permit hundreds of rows because rows will be added over time.
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853

ADVERTISEMENT

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

Code:
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

New Member
Joined
Apr 15, 2004
Messages
4
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
try this instead

Code:
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,939
Messages
5,598,967
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top