Auto Numbering Rows - Resetting Fill Number

TerranceL

Hey All,

I am looking to reset the "fill" count on our automatic row, is this possible? I've updated the prefix to match the new year and I'm looking to recent the count off the fill without changing the previous numbers generated (see snip below)

Thanks!

Jeff Reisman

The way I've done this in the past was to export the existing values to Excel as a backup, reset the column type to Text/Number, and then save the sheet. Change the column type back to AutoNumber, giving it the new prefix and starting number. New values should then start with the new prefix and starting number.

TerranceL

I'll give it a shot, thanks!

Devin Lee

Hey @TerranceL,

Not sure about changing the prefix but here are two formulas that you can use to replace the automation row. Not sure the second will work for you due to the incremental numbers being bottom to top but it's one I use on one of my sheets.

If you use this you will need to manually change the start of each year.

=LEFT(Column1, 2) + "-" + RIGHT((VALUE(RIGHT(Column1, 4)) + 10001), 4)

If you have another column with the date you can get extra fancy and use this formula which won't need any changing each year but will need to count from top to bottom.

=IFERROR(RIGHT(YEAR(Date@row), 2) + "-" + RIGHT(COUNTIF(Date$1:Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 4), "")