Auto-number based on selected category for project ID

rachel1073

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?

Paul Newcome

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.