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!
Best Answer

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

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

@GScottMiller glad it worked out!
Help Article Resources
Categories
Check out the Formula Handbook template!