How to avoid duplicates across multiple worsheets in excel?
 

Ho do I avoid making duplicate entries across a number of worksheets in the
same spreadsheet? I can avoid duplicates on the same page by using the Data
Validation function, but when I try to do this across more than one sheet I
am not able to.

Any help would be much appreciated.

How to avoid duplicates across multiple worsheets in excel?
 

You can use the following macro linked to the Workbook_SheetChange event
on the ThisWorkbook tab.

It's a bit slow and can be improved by restricting the search range to
the cells that you are likely to use.

e.g. Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(100, 100)) instead of
Sheet.Cells



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim DuplicatedValue As Boolean
DuplicatedValue = False
For Each Sheet In Sheets
If Sheet Is Sh Then
If Application.CountIf(Sheet.Cells, Target) 1 Then
DuplicatedValue = True
End If
Else
If Application.CountIf(Sheet.Cells, Target) 0 Then
DuplicatedValue = True
End If
End If
Next Sheet
If DuplicatedValue = True Then MsgBox "Duplicate"

End Sub


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=544326