Auto-Number based on year
The auto-numbering system is not always the most helpful I have learned. Can anyone assist with this formula which first takes a look at the date raised column to identify the first two parts but I want the last part to number with the next number in the sequence based on the year.
=IFERROR(YEAR([Date Raised]@row) + "-" + RIGHT("0" + MONTH([Date Raised]@row), 2) + "-" + IFERROR(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [Date Raised]:[Date Raised], YEAR([Date Raised]@row), 0), ""), ""))
Answers
-
Are you able to provide a screenshot for context?
-
-
Give this a try… Start with changing the format of the [Row ID] column to just be plain numbers (no zero filling).
Then you can use this formula in the [Risk Identifier] column:
YEAR([Date Raised]@row) + "-" + RIGHT("0" + MONTH([Date Raised]@row), 2) + "-" + RIGHT("000" + COUNTIFS([Date Raised]:[Date Raised], IFERROR(YEAR(@cell), 0) = YEAR([Date Raised]@row), [Row ID]:[Row ID], @cell <= [Row ID]@row), 4)
-
That worked, thank you! I have a few other questions about this same table that I will be posting here shortly. I am trying to recreate a presentation to auto-spit out from smartsheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!