Automatically generated number

Mathis Faucher

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 ?

Paul Newcome

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)))

Paul Newcome

Will you have more than 999 in a single month?

Mathis Faucher

No never, maximum one or two hundred per month.

Paul Newcome

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)))