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

GScottMiller

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!

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)

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)

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!