Can Smartsheet automatically highlight / notify if a reference appears on a sheet more than twice?

Options

I am literally new and learning Smartsheet and have set up a customer order form but each customer can only have a maximum of 2 free orders so i was wondering if it is possible to automatically flag if the same reference appears a third time?


At the moment i am exporting to Excel and using a countif function to check for me


Thanks

Answers

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    Options

    Formula below should solve this, just change the column name to the column where you want to check for duplicates.

    You can also create a checkbox column so it would show checked if it's true and unchecked if it's false.

    =IF(COUNTIF([YOUR COLUMN]:[YOUR COLUMN],[YOUR COLUMN]@row)>2,1,0)
    
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    My suggestion would be to insert an auto-number column with no special formatting and then a helper column (flag type in this example) with something along the lines of...

    =IF(COUNTIFS(Customer:Customer, @cell = Customer@row, [Auto-Number Column]:[Auto-Number Column], @cell <= [Auto-Number Column]@row) > 2, 1)


    This will count how many times the customer has been previously input and start flagging when for the third an on.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!