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

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    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)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!