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

Ami Veltrie

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?

Paul Newcome

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

Ami Veltrie

Hello. Thank you. I'm not sure how to make that work.

The result I'm looking for is "22-00001, 22-00002, etc"

Paul Newcome

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