Data Validation

Here is how you can start....

This is not EXACTLY what you expect, but can help you.
1. Create a List of numbers a person can use
2. Besides the list create another list, which will exclude those items which
are already selected.
You can do this by formula :
=IF(ISNA(MATCH(A1,Sheet1!A:A,FALSE)),A1,"")
Where, A1 is from the original list, Sheet1!A:A is the range where you want
to have the validation, i.e. entry row....
what it means is, if this number is already used, it shows "" otherwise the
number.
3. Create a named range for this new range, say unused_nums
4. Now create the validation with this named range.

You will see blanks in place of numbers that are already used.

I know this is not the best solution, if you have many numbers that can be blank but it does work.

HTH

Hi Daniel

I think this page will help:
Prevent Duplicates in Excel

Hi Admin, (Quite sure this is not Dave's Update)

The issue is, Daniel wants to have the list and also avoid duplicates.
So just avoiding duplicates does not solve the issue.

Just a clarification

try this one

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set fn = Application.WorksheetFunction
Set testrange = Range("A1:A10") 'input range
With testrange.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$G$1:$G$10" 'validate list here
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
If fn.CountIf(testrange, ActiveCell.Offset(-1, 0).Value) > 1 Then
MsgBox "You try to input double value"
ActiveCell.Offset(-1, 0).Value = ""
ActiveCell.Offset(-1, 0).Select
Exit Sub
End If
End Sub

The attached expands on yjoshi's idea, but it removes the blanks. The workbook validation is set for A1:A9. The remaining data should be on another hidden sheet but I left it on the first sheet so you could see how it works. Column C has the original master list. Columns D and E are intermediate calculations. Column F has the compressed list of eligible. A dynamic range called ShowList then adjusts to pick the entries in column F abobve the NA entries. ShowList is the range assigned to the validation for A1:A9. You should be able to adapt this to your situation.

Duplicate validation

If I understand you correctly, you want to verify that there are no duplicates in a list. This is easy using the data validation function. Use the following rule in the validation window, applied to all cells in the range. The second cell reference (with no $) references the specific cell having the validation; the range is all cells to be checked.

=COUNTIF(A$1:A$23,A8)=1

I could get across avoiding blanks inbetween the list.

Now you get only the UNUSED numbers in the list.

Hope this helps now.

See the sheet attached.

Deniel (Derk),

I uploaded my sheet without seeing your update.
Actually this was eating my head whole night and had thought of the solution at night as well. I just implemented it and uploaded without seeing yours.

Anyway.... both of us are on similar lines, with minor adjustment in techniques.

Thanks