Automatically generated number


I am trying to build on Smartsheet a column that print a new consecutive number each time a request from a form is received. I want the following property for the numbers:

  • consecutive numbers
  • when 999 is reached, start again from 1
  • when the month changes, start again from 1

I have managed to build the feature with the changing month, but I cant turn it in a column formula because it contains a "cell reference" in the formula and because the formula dont works in the first cell of the column.

My formula in the column "number" is :

=IF([Month letter]@row=[Month letter]1,IF(Number1 < 1000, Number1 + 1, 1),1)

Do someone have an idea ?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Then really we won't need to worry about resetting after 999 since the monthly reset would happen first.

    I would insert an auto-number column (called "Auto" in this example) with no special formatting. Then you can use a column formula such as this...

    =COUNTIFS(Auto:Auto, @cell<= Auto@row, Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(Date@row), IFERROR(YEAR(@cell), 0) = YEAR(Date@row)))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!