Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    only allow unique value in cell (xp 2003)

    When creating a new file, we assign a unique numer of the form ABC1234XYZ which goes in one column, other columns hold eg dates, descripton, originator etc. Sometimes a file gets given an existing file number in error, and we don't notice until later. Can I have excel check each time to disallow an existing file number being entered in the cell? We only need to check the items in that column, I think there will likely be fewer than 5000 rows in the worksheet, so not a huge amout to check. I looked at validation under the data menu, but that did not seem to do what I want. Thanks Frank

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: only allow unique value in cell (xp 2003)

    Let's say the values in column A should be unique.
    Click the column header of column A to select the entire column. A1 should be the active cell within the selection.
    Select Data | Validation...
    Select Custom from the Allow dropdown.
    Enter the following formula in the Formula box:
    <code>
    =COUNTIF(A:A,A1)<2
    </code>
    Activate the Error Alert tab.
    Enter an appropriate error message, e.g. Duplicates are not allowed in this column!
    Click OK.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: only allow unique value in cell (xp 2003)

    Thank you Hans, that worked perfectly.
    Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •