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)))
