Auto-number based on selected category for project ID

Options
rachel1073
edited 12/09/19 in Smartsheet Basics

I track all projects under ~7 programs in a Smarsheet. I have auto-numbering on for the column, but it numbers new projects at "PT135" or whatever. I'm looking for a way for it to only auto-number based on the selected program, so if I select Beverage, the project ID will be "BEV135," or an edible would be "EDB123." Is there a workaround that could help me achieve this without doing it manually?

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Yes. It is very possible. You would need to figure out your exact criteria and the exact output you would want. Column names included in your breakdown would certainly be helpful.

     

    It would look something like this...

     

    =UPPER(LEFT([Program Column]@row, 3)) + COUNTIFS([Program Column]:[Program Column], [Program Column]@row, [Date Column]:[Date Column], @cell <= [Date Column]@row)

    .

    What this would do is pull the first 3 characters from the Program (in your example "Beverage") and capitalize them. It would then count how many other times "Beverage" was selected on past dates (meaning previous entries) and tack that number on to the end.

     

    If there is the possibility of multiple rows being added for "Beverage" on the same date, we would need to modify it to ensure a unique row id for each entry, but a lot of that is going to be determined by your specific setup and requirements.

     

    If you would like to provide more details, I am sure we could get something figured out for you.