How to prevent double entry in excel?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Biff (biffinpitt_at_comcast.net)
Date: 03/15/05 

Date: Mon, 14 Mar 2005 23:04:36 -0800

Hi!

You can use Data Validation to do this.

Assume you'll be entering data in the range A1:A100 and
want to prevent any duplicate entries.

Select the range A1:A100
Goto Data>Validation
>From the Allow drop down select Custom
In the Formula box enter:

=COUNTIF($A$1:$A$100,$A1)<=1

If you want, you can create a custom message that will pop
up if a duplicate entry is attempted.

Click the Error Alert tab.

Use a message something like this:

You are attempting to make an entry that already exsists!

Click OK and you're all set!

Biff

>-----Original Message-----
>Hi,
>
>I'm trying to avoid double entry in this particular work
here. Is there any
>formulas or function that allow me to automatically
detect the double entry?
>
>George
>.
>

Relevant Pages

  • Re: preventing duplicate entries
    ... You can use Data Validation for that. ... You can also set a custom message to pop up whenever a wrong entry is ... > complication in that duplicate data can be allowed if the ...
    (microsoft.public.excel.misc)
  • Re: add multiple words to custom dictionary Microsoft Word
    ... >> capital letters in the entry, the entry will match only text words ... >> Avoid any characters not in the code page 1252. ... >> Sorting is not required and duplicate entries do not hurt; ...
    (microsoft.public.word.newusers)
  • Cruise control quilting Re: Directory is now closed
    ... Like a catalog? ... but the last entry was made last night at 10:56 pm from New ... duplicate entries, the last submission will be considered the "official" ... Everyone should receive only one email test. ...
    (rec.crafts.textiles.quilting)
  • RE: Can anyone povide step by step instructions on how to do the f
    ... to get the budget category entry and date from and another cell to enter the ... entry changes and go put the amount in the proper cell. ... If Excel's Help on topics such as Data Validation, ...
    (microsoft.public.excel.misc)
  • Re: Data Validation - copy/paste
    ... You can use a Worksheet_Change event macro to look at the entry whenever ... That macro can then search the Data ... Private Sub Worksheet_Change ... validation but also over-write the data validation. ...
    (microsoft.public.excel.programming)