Preventing Duplicate Records

[Chris]

Hi, could someone please help me with the following formula which I am
trying to use to prevent duplicate records in column A of my Excel 2003
worksheet:

I use Data - Validation - Allow = Custom and Formula =
COUNTIF($A$3:$A$65000,A1)=2

My data starts at cell A3. I want to include two only duplicate records
in column A. So, if the user enters the same text value a third time an
error message pops-up warning of a duplicate record.

For example (Position Numbers): E-10-0010 is in cell A3 and A4.
E-10-0012 is in cell A5 and A6. If I enter in for example another
Position Number: E-10-0010 in cell A7, then this would be the third time
that that Position Number has been entered into column A and I need
excel to reject its entry into the cell and then display a pop-up
warning message.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

 

[Mike H]

Hi,

Try this data validation formula

=COUNTIF($A$3:$A$65000,A3)<2

Mike

 

[Chris]

Thanks Mike - works well now - greatly appreciated.

Cheers,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***