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
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
-
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
-
Angus,
I hope my outline was useful. Did you have any success with this yesterday?
-Brett
-
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
-
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.
-
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.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives