Custom Auto Numbering Function
Hi! New Smartsheet user here, appreciate any help and guidance. I'm looking to create a formula to build a custom autonumber function. I'm including a screenshot for reference. I'd like to automate the Project ID column. Where the project ID is YEAR  CLIENT  CLIENT PROJECT NUMBER.
Is there a formula out there that would work for this? Thank you very much for taking the time to review.
Best Answer

You would just need to rearrange the sections.
You can see in the formula there are two instances of
+ "" +
Those are your delimiters. Everything to the left of the first one is what outputs the "R". Everything to the right of the second one outputs the "20", and everything in between outputs the count.
So right now the formula is going
left + "" + middle + "" + right
You want
right + "" + left + "" + middle
Answers

You are going to need to insert an autonumber column with no special formatting.
Then you can use this:
=RIGHT(YEAR([Start Date]@row), 2) + "" + Client@row + "" + COUNTIFS([Auto number]:[Auto Number], @cell<= [Auto Number]@row)

Thank you! I actually just stumbled across another one or your helpful responses here: https://community.smartsheet.com/discussion/69133/customautonumber
This is exactly what I'm looking to do but now I'm just trying to tweak the formula so it goes Year  Project Class  Unique Number. So Instead of R120 it'd be 20R1
=LEFT([Project Class]@row + "" + COUNTIFS([Project Class]$1:[Project Class]@row, [Project Class]@row, [Start Date]$1:[Start Date]@row, YEAR(@cell) = YEAR([Start Date]@row)) + "" + RIGHT(YEAR([Start Date]@row), 2)

You would just need to rearrange the sections.
You can see in the formula there are two instances of
+ "" +
Those are your delimiters. Everything to the left of the first one is what outputs the "R". Everything to the right of the second one outputs the "20", and everything in between outputs the count.
So right now the formula is going
left + "" + middle + "" + right
You want
right + "" + left + "" + middle

Thank you!! It worked :)

Help Article Resources
Categories
Check out the Formula Handbook template!