Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Unique Values


I searched Help and the Community for "unique value" and did not find anything. 


Is there a way for a field in a sheet to be unique?


For example, I want the Document ID column to be unique. Let's say I do not want to use the Auto Number property. Is there a way to ensure that all values are unique in a column when anyone enters a value?


  • KrisWalsh
    KrisWalsh ✭✭✭✭✭

    The entire purpose of the Auto-Number column is to accomplish this. What issue are you running into with it?

  • We started using the Auto-Number, but sometimes records would get created by users by mistake. Then, we would have to delete the Auto-Number column, delete the erroneous records, re-add the Auto-Number column and the Auto-Number would be fine. 


    We were thinking to manually control the ID #, but I wanted to check if I can protect against duplicate values in the field.

  • KrisWalsh
    KrisWalsh ✭✭✭✭✭

    Example Sheet - Feel free to play around with it.  


    The purpose of the Auto-Number column is to give you a unique value.

    When someone mistakenly adds a row & you delete it, the next value will be one higher. 


    Row 9 - Good Entry - ID# 10009

    Row 10 - Mistaken Entry - ID# 10010


    Row 10 Deleted

    New Row 10 - Good Entry - ID# 10011


    I played around with it in the Example Sheet to double check. I added and deleted columns and always get a new, unique, higher value for the new row on the sheet.


    Unless you want/need your ID #'s to be exactly sequential, I can't see a reason to try something else.  (like a formula that could break)


    Hope this helps!  (-:

  • Travis
    Travis Employee
    edited 02/19/16

    One option is to add a column with a formula that checks if the corresponding ID is a duplicate, then use conditonal formatting to color the ID background if it is a duplicate.


    Here's an example:



  • Thank you Kris and Travis!

    Both very helpful!

  • Mohd

    Hi Travis
    Can you please give me formula to check duplicate?


  • KrisWalsh
    KrisWalsh ✭✭✭✭✭

    You can hover over his embeded sheet & see the formula. Smile

    He also used Conditional Formatting to make the ID column turn red. 


    Travis' Formula:

    =IF(COUNTIF(ID:ID, ID1) > 1, 1)


    If your column name is "ID #"   *has a space in the name*

    =IF(COUNTIF([ID #]:[ID #], [ID #]1) > 1, 1)


    With absolute reference to column. *best practice*

    =IF(COUNTIF($[ID #]:$[ID #], $[ID #]1) > 1, 1)

  • Mohd
    edited 02/27/16

    Hey Kris

    Thanks a lot

This discussion has been closed.