Custom Auto Numbering Function

BrendonP

Hi! New Smartsheet user here, appreciate any help and guidance. I'm looking to create a formula to build a custom auto-number 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.

Paul Newcome

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

Paul Newcome

You are going to need to insert an auto-number 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)

BrendonP

Thank you! I actually just stumbled across another one or your helpful responses here: https://community.smartsheet.com/discussion/69133/custom-auto-number

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 R-1-20 it'd be 20-R-1

=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)

Paul Newcome

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

BrendonP

Thank you!! It worked :)

Paul Newcome

Happy to help. 👍️