Don't allow Non-Unique Entry in Excel 1

[JamieArvato]

Don't ask ! but we keep our HR database as a single table in an Excel file....I know !!??!?

How can I ensure that each employee number that is keyed has not been used before ? I was thinking on using Worksheet_Change and then looping through every row between the one I'm on and the top and checking and if it finds an = then Application.Undo and MsgBox but don't know exactly how to write the code.

Thanks.

 

[ETID]

You could flag the entry with a conditional format formula
so that at least you would know by color of text that the number had been used.

Highlight the entire column..(I USED COL. A), then Format>Conditional formatting

and type the following under the "formula Is" condition
....NOTE: change A:A to your col ref.

=if(countif(A:A,A1),TRUE,FALSE)

...

set the format...{Red text maybe?)

click ok

 

[ETID]

Ooops wrong formula....

=if(countif(A:A,A1)>1,TRUE,FALSE)

 

[SkipVought]

Hi,

How about Data/Validation - Custom - Formula
[tt]
=COUNTIF($A:$A,A1)=1
[/tt]


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[000Steve000]

This does what you described. Hope it helps

Private Sub Worksheet_Change(ByVal Target As Range)
ColToCheck = 1
CurrentRow = Target.Row
For i = 1 To CurrentRow - 1
    If Cells(i, ColToCheck) = Cells(CurrentRow, ColToCheck) Then
        Duplicate = True
        Exit For
    End If
Next i
If Duplicate = True Then
    Duplicate = False
    Cells(CurrentRow, ColToCheck) = ""
End If
End Sub

 

[ETID]

I was just going there skip...

I used this....NOTE: change A:A to your col ref.

Highlight the entire column..(I USED COL. A), then from menu... Data>Validation

and select allow: "Custom"

THEN IN FORMULA: BAR...

=if(countif(A:A,A1)>1,FALSE,TRUE)

YOU CAN TYPE A CUSTOM MESSAGE TO USERS FROM THE MIDDLE TAB.