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 ?

Find more posts tagged with

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.