How to get "Auto-Number" to AUTOMATICALLY start over at "1" on the first of each year?

I'm using auto-number on several sheets. Each is set up to include the year.
How do I get it to start over at "1" at the beginning of each year WITHOUT having to go into the sheet and manually edit the 'Starting Number' value?
Answers
-
You would need to create a helper column with a formula instead of using the Auto-Number feature.
The idea is to use a COUNTIFS to count how many row were created within the same year but prior to the "current row".
=COUNTIFS(Created:Created, AND(@cell <= Created@row, IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))
-
Hello. Thank you. I'm not sure how to make that work.
The result I'm looking for is "22-00001, 22-00002, etc"
-
Your formula would look something like this:
=RIGHT(YEAR(Created@row), 2) + "-" + IF(COUNTIFS(Created:Created, AND(@cell <= Created@row, IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 10000, "0", IF(COUNTIFS(Created:Created, AND(@cell <= Created@row, IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 1000, "00", IF(COUNTIFS(Created:Created, AND(@cell <= Created@row, IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 100, "000", IF(COUNTIFS(Created:Created, AND(@cell <= Created@row, IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 10, "0000")))) + COUNTIFS(Created:Created, AND(@cell <= Created@row, IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 151 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives