Preventing double entry formula

[Yoli]

I am creating a phone list with telephone number for our employees to use
when they are calling our clients. I want to prevent a phone number being
entered on the call list multiple times. Is there a way that I can enter a
formula that will give me either a stop or warning message when a phone
number is already on my spread sheet? Please Help.

 

[Fred Smith]

The typical solution is to use Countif. If your phone numbers are in column
A, use:
=Countif(A:A,a1)
And copy down.

A result greater than 1 indicates a duplicated number.

Regards,
Fred

 

[Don Guillett]

Right click sheet tab>view code>insert this>change column 1 to suit

Private Sub Worksheet_Change _
(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Application.CountIf(Columns(1), _
Target) > 1 Then MsgBox "Duplicate"
End Sub

 

[Eva]

To prevent double entry formula please use Data Validation feature
Select the range then:

Data / Validation / Settings / Allow / Custom / Formula
=SUMPRODUCT(--(EXACT(A1,$A$1:$A$250)))=1

then to to Error Alert and enter the message "Double entry"
Click yes if helped

 

[Ashish Mathur]

Hi,

Assume that phone numbers are entered in range A3:A50. Click on cell A4 and
go to Data > Validation > Allow > Custom > Formula and enter

=countif(A$3:A4,A4)=1

Copy this validation down by Copy > Edit > Paste Special > Validation

Now if any duplicate number is entered, a pop up will appear.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com