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

Options
✭✭✭✭✭✭

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭✭
Options

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