Data Validation : Limiting Entry Of Names

  • As I enter information into a worksheet, I am using a data validation
    list to check the entries. Only names on the data validation list can
    be entered. Is there a way that I can remove a name from the data
    validation list so that it can only be entered once? In other words, I
    want only names from a particular list to be valid for entry, but they
    can only be entered once.

  • Hi,

    Welcome to the board :)

    I don't believe that you can have two sets of data validation applied to the same range, so your list option precludes automatically preventing duplicates with data validation.

    However, you could use conditional formatting to highlight any duplicates. Assume that you are entering data from A1 downwards. Select conditional formatting for A1 and select the 'formula is' option. For the formula enter "=COUNTIF($A$1:A1,A1)>1" and then apply your format, eg a red background. Use the format painter to copy this down as far as required. Any duplicate entries will now be highlighted.


Participate now!

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