Not Repeating An Entry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John13 - 03 Oct 2006 13:24 GMT

I have 13 columns of information and column A is Order Number. What is
the best way to not enter the same order number twice, short of doing a
Find prior to entering it? I was trying to do a conditional format
that if it equalled and other cell in column A, turn the cell
background Red. Thanking you in advance your help.

John

Reply to this Message

Dave Peterson - 03 Oct 2006 13:39 GMT

You could use Format|Conditional formatting to highlight the duplicates:
(with a custom formula of)
=countif(a:a,a1)>1

Or you could use Data|Validation
and a custom formula of:
=COUNTIF($A$1:A1,A1)<2
(with A1 the active cell)

> I have 13 columns of information and column A is Order Number. What is
> the best way to not enter the same order number twice, short of doing a
[quoted text clipped - 3 lines]
>
> John

Signature

Dave Peterson

Reply to this Message

John13 - 04 Oct 2006 14:00 GMT

Dave,

Thank you, the Conditional Formatting worked great. I couldn't get the
Data|Validation to work and was wondering if you could walk me through
the steps. Thank you.

John13

> You could use Format|Conditional formatting to highlight the duplicates:
> (with a custom formula of)
[quoted text clipped - 12 lines]
> >
> > John

Reply to this Message

Dave Peterson - 04 Oct 2006 14:30 GMT

My data was in A1:A10 (say)
I selected A1:A10 and with a1 the activecell
I did data|validation
On the settings tab
Allow: Custom
Formula: =COUNTIF($A$1:A1,A1)<2

and finished up the other tabs

Then if I typed: ASDF in A1
and tried to type ASDF in A2:A10, I'd get the warning.

> Dave,
>
[quoted text clipped - 24 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Don Guillett - 03 Oct 2006 14:03 GMT

right click sheet tab>view code insert this.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set mr = Range(Cells(1, 1), Cells(lr, 1))
On Error GoTo leaveit
If mr.Find(Target) Then MsgBox "Pick Another Number"
leaveit:
Application.EnableEvents = True
End Sub
Sub fixit()
Application.EnableEvents = True
End Sub

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I have 13 columns of information and column A is Order Number. What is
> the best way to not enter the same order number twice, short of doing a
[quoted text clipped - 3 lines]
>
> John

Reply to this Message