Unique Value Check Using Data Validation

[JamieArvato]

I found what I thought was a perfect solution to a problem I have in putting in the custom data validation below warning of duplicate values in a column;

=IF(COUNTIF(A:A,A12)>1,FALSE,TRUE)

BUT........

I already had this column as a list data validation so that people could only pick from a particular list (being a 1,000 name staff list so not something I can do without due to mis-spelling etc.)

What I really want is both, pick from a list which then checks and warns if used before ?!?

The only way I can see is a Worksheet_Change code to loop through all the cells and search for a match. If this is the case can anyone help with that....

It would be on changing range("A310:A360"), check for a match in range("A10:A309"), then a simple if then MsgBox.

Thanks.

 

[Loomah]

Hi Jamie
I'm not too sure I fully understand what you're asking for here but the following will warn that a vlue selected in data validation has already been used in a list.

In my case the list being tested is A1:A30. The cell that is being changed is B1. The list used for the validation list is a separate list of unique values stored in a different sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
    If [countif(A1:A30,B1)] > 0 Then
        MsgBox "Value Already Used"
        [b1].ClearContents
        [b1].Activate
    End If
End If
End Sub

I hpe I'm not too far from what you are actually looking for here!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​