Auto Numbering Rows - Resetting Fill Number


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)



  • Jeff Reisman
    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.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • TerranceL

    I'll give it a shot, thanks!

  • Devin Lee
    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), "")