Auto Generate sequential # to different Project ID category


Hi there,

I am looking for a formula or a solution to auto generate Project ID with sequential # once Project Type was chosen in Intake Form. I described what I would like to achieve in detail in an attached document. Could you kindly take a look at it and let me know if this is something SmartSheet can handle?

Best regards,




  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My suggestion is going to start with building out a table that houses the different options in one column and the corresponding abbreviations in another column. You are also going to need an auto-number column with no special formatting.

    Then the formula to pull the year, pull the abbreviation, the increment the number based on year/abbreviation would be:

    =YEAR([Submitted Column]@row) + "-" + INDEX({Table Sheet Abbr Column}, MATCH([Project Type]@row, {Table Sheet Project Type Column}, 0)) + COUNTIFS([Auto-Column]:[Auto-Column], @cell<= [Auto-Column]@row, [Submitted Column]:[Submitted Column], IFERROR(YEAR(@cell), 0) = YEAR([Submitted Column]@row), [Project Type]:[Project Type], @cell = [Project Type]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!