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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!