Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Autonumber refresh for new year

Options
Angus Bruce
edited 12/09/19 in Archived 2016 Posts

I use a formatted autonumber column to record a new Reference Number for each new entry in a Smartsheet.  The format I use is CR{YY}.000 where 000 started from 001 for the year 2016.  I want to continue this format into 2017, but I want the 000 to restart from 001.  Any thoughts ?

 

My list of rows should end up like :

CR16.001

CR16.002

CR16.nnn

CR16.999

CR17.001

CR17.002

CR17.nnn

CR17.999

Comments

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭
    edited 12/08/16
    Options

    I don't have time to do the formulas today other than sketch out the direction they might go in, but I would try the following:

     

    In a hidden area of the sheet:

    1) Use autnumber to keep a count 1, with no prefix

    2)Create a Createddate system column
    3) use countif to count the # of 2016 entries store this value in a cell, entries2016

     

    Your column to display nubers would have a formula something like this:

    4) if (Year(Createddate ) = 2016, "CR16"+Autonumber, If(Year(Createddate )  = 2017, "CR17"+Autonumber- entries2016))

     

    If you aren't familiar with the Smartsheet formulas, perhaps someone else could comment if this is on the right track or not.

     

    -Brett

     

     

     

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭
    Options

    Angus,

    I hope my outline was useful.   Did you have any success with this yesterday?

     

    -Brett

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Angus,

     

    You should be able to do it this way:

     

    1. Change the Autonumber column to a Text/Number column.

    2. Close the sheet (this step did not used to be required, but is now. I'm not sure when or why it changed)

    3. Reopen the sheet.

    4. Change the Autonumber column back to a System | Autonumber column with your chosen details:

     

     

    5. Save the sheet.

     

    New rows should start the correct numbering. Existing entries from 2016 won't be affected.

     

    You'll want to do this after all 2016 entries are entered.

    You can, however, use the same process to change entries between steps 3 & 4.

     

    Hope this helps.

     

    Craig

    Autonumber.jpg

  • Angus Bruce
    Options

    Brett, Craig, thankyou both for responding.  I've been off with a heavy cold and only just starting to surface again.  I'll review both and let you know how I get on - although on the face of it Craig's sounds like the way I would want to go.  I'll send an update tomorrow.

  • Angus Bruce
    edited 12/15/16
    Options

    I've used both methods to create new 2017 based Change Requests and both work very well.  The method I'm going to choose is Craig's as being the simplest to describe in the document description.  

     

    Many thanks to you both for your help.

  • Angus Bruce
    edited 01/09/17
    Options

    Just to let you know,  2017 started off well, using Craig's suggestion, on my fifth change request now :-)

    I didnt actually have to close the sheet, I did Save however and that seems to have helped things along.

This discussion has been closed.