Auto Generate sequential # to different Project ID category

Yoko Yabe

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,

Yoko

auto generation number formula in smartsheet.pdf

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)