Looking to create a Auto Numbering System that is a little more advanced. [Help!]

Hey everyone!

Looking to create a numbering system for Change order Generation for my company. Currently the Auto Numbering from smartsheet is a little simple for our uses. I'd like to come up with a formula that checks if any other change orders exsist for a given project number, then gives is a 1,2,3 after that.

So the format could be something akin to this

CO_[Project Number]_[Change order number]

That change order number would sequencially restart for each new project. E.g. for project 123456 we would have CO #1 be "CO_123456_1" and the second CO for that Project would be "CO_123456_2" and then it would restart for each new project number.

I am happy to make up some helper columns and use advanced formulas, I just would love some help in the right direction!

Find more posts tagged with

Formulas

Accepted answers

Lucas Rayala

Hi @GScottMiller -- I'll assume that your project column is titled "Project" and you have a RowID column called RowID that simply numbers the rows 1,2,3, etc. You will need this row ID to be a number to do some calculations.

Try this:

="CO_"+ RowID@row+ "_"+COUNTIFS(Project:Project, Project@row, RowID:RowID, <=RowID@row)

All comments

Lucas Rayala

Try this:

="CO_"+ RowID@row+ "_"+COUNTIFS(Project:Project, Project@row, RowID:RowID, <=RowID@row)

GScottMiller

@Lucas Rayala I had to mix it up a little bit, but that worked perfectly! Thanks!

My Final formula was as follows (for anyone who might find this in the future)

="CO_" + Project@row + "_" + COUNTIFS(Project:Project, Project@row, RowID:RowID, <=RowID@row)

Lucas Rayala

@GScottMiller glad it worked out!

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.