How to restrict Duplicate entries in excel?

[Sharafuddeen Sharaf]

Dear Gurus,
Kindly help me out for controlling the duplicate entries in one excel workbook not in a sheet or a specific range.

 

[vletm]

Do You mean that
there can be only once information, for example "Jul",
in the whole Excel workbook?

 

[Sharafuddeen Sharaf]

Yes. I mean so. for eg: I keeps multiple sheet in one book for preparing invoices and wanna to keep a unique invoice no.

 

[xlstime]

means for specif columns like invoice date etc.

Yes. I mean so. for eg: I keeps multiple sheet in one book for preparing invoices and wanna to keep a unique invoice no.

 

[vletm]

Please, think two more times what do You really want and after that try to write it as clear as possible.
Your original message has different meaning than Your second message...
and don't forget 'Upload a Sample File to get a quicker response' with sample results.

 

[Sharafuddeen Sharaf]

I have an excel work book in which i keeps invoices in every sheet with unique invoice no but sometimes by mistake the invoice number gets repeated. It should not happen.

 

[Chihiro]

You can use FIND method I suppose. But in order for us to help you, you need to specify your data structure (i.e. which column in each sheet contains Invoice Number etc).

As vletm pointed out, uploading file with enough dummy data so we can clearly understand your data structure and expected behavior will help you get desired answer quickly.

 

[Sharafuddeen Sharaf]

Dears, I have attached the invoice-book here. If i need to raise a invoice, i would make a copy of one of the existing invoice sheet and change the invoice no. etc, What i want is, when i type the new invoice no in a specific cell (F15) system should check all the other sheets in this workbook to make sure that the newly entered invoice no. doest not appear in anywhere else so that invoice no. is not duplicated. Otherwise, if an invoice no. automatically generates in ascending series in the specified coloumn when i copy an invoice sheet also will do.

 

[vletm]

This Sample would check Invoice no (from cell F15)
after You have pressed <Enter>,
if duplicate then You'll get notes.

 

[Chihiro]

Here's another method. Paste into each worksheet module. If you make copy of existing worksheet using Move or Copy, code will be replicated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim invoiceString As String

If Not Intersect(Target, Cells(15, 6)) Is Nothing Then
    If Not IsEmpty(Target) Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> ActiveSheet.Name Then
                invoiceString = invoiceString & ws.Cells(15, 6).Value
            End If
        Next ws
        If InStr(1, invoiceString, Target.Value, vbTextCompare) > 0 Then
            MsgBox "Duplicate Found"
            Target.Activate
        End If
    End If
End If

End Sub