How do I prevent duplicate entries across sheets

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.

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

regards,
Erik

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

How many rows are used in the columns?

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

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

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?

try this instead

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