Data Validation

  • Currently the Data Validation that I have set up for the spreadsheet is that I have the user select a number from a list. What I also would like to do is not allow duplicate entries. How do I incorporate the two validations - if possible?



  • 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 :
    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
    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.


    Thanks: ~Yogendra

  • 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 :)

    Thanks: ~Yogendra

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

    If you walked away smiling-then for you the price was right

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


  • 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: ~Yogendra

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!