Autonumber refresh for new year

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

Find more posts tagged with

Sheets

Services

Support

IT & Operations

Project Management

Comments

Brett Evans

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

Angus,

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

-Brett

J. Craig Williams

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

Angus Bruce

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

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

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.