Automatically generated number

Hello,
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
-
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)))
Answers
-
-
No never, maximum one or two hundred per month.
-
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
Categories
Check out the Formula Handbook template!