Thread: Validating Data To Be Unique In A Range

For some reason when I open the excel file that has a DT Picker box in it, the box is not the formatted shape and has a red x behind it. However, if I open another workbook and go back to the initial sheet it is fine. I need the DT picker for presentation purposes and it is a bit ackward having to open a new sheet and delete it just so the DT Picker box goes back to normal. Any ideas..?

For some reason when I open the excel file that has a DT Picker box in it, the box is not the formatted shape and has a red x behind it. However, if I open another workbook and go back to the initial sheet it is fine. I need the DT picker for presentation purposes and it is a bit ackward having to open a new sheet and delete it just so the DT Picker box goes back to normal. Any ideas..?

Problem:

Validiating the values entered in column A, so that a value could not be entered more than once.

Solution:

Select column A-->Data-->Validation-->choose Custom-->Enter this formula:
=COUNTIF($A:$A,$A1)
Thus, when trying to enter \"\"1\"\" in cell A10, an error message will pop out,
and will prevent the user from entering any of the values already stored in column A.

Neat trick. Is there a way to customize the error message?

--GJC

A solution is given as
Select column A-->Data-->Validation-->choose Custom-->Enter this formula:
=COUNTIF($A:$A,$A1)
The $A1 should not be absolute? And the formula doesn't seem to work

The following works:
=COUNTIF(A:A,A1)=1

Regards
Brian

I don't believe that Data Validation allows you to customize your error messages dynamically. However, here is one workaround: Copy the code below into the code for your worksheet (Alt F11, and Double Click on the sheet name in the Project explorer on the left, and paste into the code area on the right)

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Value
        Case "": Case Else
            If Not Application.Intersect(Target, [A:A]) Is Nothing Then
                Select Case Application.WorksheetFunction.CountIf([A:A], Target)
                    Case 1: Case Else
                        myMatch = Application.WorksheetFunction.Match(Target, [A:A], 0)
                        myPrompt = "Value below already exists in cell $A$" & myMatch & _
                        Chr(10) & "Please modify your entry"
                        myDefault = Target
                        myInput = InputBox(Prompt:=myPrompt, Default:=myDefault, Title:="ExcelTip")
                        Target = myInput
                End Select
            End If
    End Select
End Sub

This also allows you to modify what you have already entered and tells you in which cell the duplicate entry exists. The routine will loop correctly if you continue to enter subsequent duplicate values.
You can develop elaborate custom messages.
Regards
Brian

This is a fantastic tip!

Brian, I like how your code tells the user where the original entry is located.

GJC, for a simple custom message, this works great:

In the Data Validation dialog box, go to the Error Alert tab to customize your error message. You can also customize the Input Message

on its tab.

Hope this helps!
~SharonFinLV

Is there a way to apply this validation across all the worksheets in the workbook? That is for this example can you check all values in the A column in every sheet in the workbook for uniqueness when entering a value?

Thanks,

Paul

Copy / PasteSpecial Validation