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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!