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.
--
Yoli

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


"Yoli" wrote in message
...
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.
--
Yoli

Right click sheet tabview codeinsert thischange 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Yoli" wrote in message
...
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.
--
Yoli

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
--
Greatly appreciated
Eva


"Yoli" wrote:

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.
--
Yoli

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

"Yoli" wrote in message
...
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.
--
Yoli