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,
Yoko
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!