» Validating Data To Be Unique In A Range

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)[CODE]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[/CODE]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

[SIZE=2]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:

[INDENT]In the [B]Data Validation[/B] dialog box, go to the [B]Error Alert[/B] tab to customize your error message. You can also customize the [B]Input Message[/B]
[/INDENT] on its tab.

Hope this helps!
[COLOR=Purple]~SharonFinLV[/COLOR][/SIZE]

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

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