Create a custom auto increment based on a condition
I am trying to generate unique ID codes for each line item. The ID codes are: TYPE + "-" + YEAR + "_"+ #ofTypeinYear + "_" + PLACE.
I am struggling to create the "#ofTypeinYear" column. This column should be able to scan the ID CODE column, and find the max "#ofTypeinYear" in the code for the items that have the same type and same year, and increment by one.
The sheet has an associated form, and every time there is a new submission row, I would like to autogenerate a unique ID Code. Do I need to create a series of linked sheets to do this?
Answers
-
You would need to insert an auto-number column (called "Auto" in this example) with no special formatting. Then you would use a COUNTIFS along the lines of
=COUNTIFS(Type:Type, @cell = Type@row, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(Date@row), Auto:Auto, @cell <= Auto@row)
-
Thank you so much, Paul! This was very helpful :)
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives