How to add a prefix to a project ID

Ddabbs · 12/22/22

Hi Community,

I am trying to create a project ID column that generates a prefix based on the project type. I'd like the numbers to go in sequential order for each project.

Ex: abc001, abc002, def001, abc003, def002

Additionally, I would like for it to only generate a number if another column specifies "New Project."

Any input would be much appreciated!

Mike TV · 12/22/22

@Ddabbs

Create an Auto Number column with your preferred number format. Then create a column which builds your Project ID based on another column which helps determine the abc or def prefix and then adds the Auto Number.

You'll need to create a formula which is something like:

=IF([Project Type]@row="Alpha", "abc"+[Auto Number]@row, IF([Project Type]@row="Beta", "def"+[Auto Number]@row))

Jeff Reisman · 12/22/22

@Ddabbs I think this question gets asked 3-4 times year.

There are a few ways to do this, but they all start with having separate intake sheets for each project type, and moving those rows to a main project list sheet. This is because you need independent Auto-number columns for each project type, and the only way to do that in core Smartsheet is with separate sheets.

Alternatively, you could have one intake sheet without an Auto-number column, which then moves rows to separate project type sheets based on project type where each project sheet contains a prefixed Auto-number column, and have automation move those rows to a main project sheet. That flow would look like this:

Mike TV · 12/22/22

@Ddabbs

Create an Auto Number column with your preferred number format. Then create a column which builds your Project ID based on another column which helps determine the abc or def prefix and then adds the Auto Number.

You'll need to create a formula which is something like:

=IF([Project Type]@row="Alpha", "abc"+[Auto Number]@row, IF([Project Type]@row="Beta", "def"+[Auto Number]@row))

Jeff Reisman · 12/22/22

@Ddabbs I think this question gets asked 3-4 times year.

There are a few ways to do this, but they all start with having separate intake sheets for each project type, and moving those rows to a main project list sheet. This is because you need independent Auto-number columns for each project type, and the only way to do that in core Smartsheet is with separate sheets.

Alternatively, you could have one intake sheet without an Auto-number column, which then moves rows to separate project type sheets based on project type where each project sheet contains a prefixed Auto-number column, and have automation move those rows to a main project sheet. That flow would look like this:

Ddabbs · 12/22/22

@Jeff Reisman Thank you for the response! Yes this is exactly what I was looking for.

Jeff Reisman · 12/22/22

Happy to help!

Paul Newcome · 12/22/22

I personally use a variation on @Mike TV's solution, but instead of using the auto-number column to get the number, I use a COUNTIFS so that I can have each type incremented individually such as

ABC001

ABC002

DEF001

ABC003

DEF002

It looks similar to this:

=IF([Project Type]@row="Alpha", "abc", IF([Project Type]@row="Beta", "def")) + COUNTIFS([Project Type:]:[Project Type], @cell = [Project Type]@row, [Created Date]:[Created Date], @cell,= [Created Date]@row)

You can get leading zeros like so:

=IF([Project Type]@row = "Alpha", "abc", ...........) + IF(COUNTIFS(..........)<10, "00", IF(COUNTIFS(..........)<100, "0", "")) + COUNTIFS(..........)

@Jeff Reisman I had honestly never thought to do it that way. I personally like to avoid automations and extra sheets when possible, so I just started using the above.

It can adjust them if you have an archiving system in place because it counts the rows on the current sheet, but then you would only need one extra sheet with one automation to just push the row over once the ID is created instead of having to go through multiple sheets with multiple automations.

Jeff Reisman · 12/22/22

@Paul Newcome

I knew I'd seen MikeTV's solution out there, just couldn't remember how to find it. His solution works provided nobody ever deletes a row or moves a row off the sheet. That's the real drawback to using COUNTIFS for this.

Paul Newcome · 12/22/22

@Jeff Reisman Right. That's what I was getting at with the Archiving. You can avoid this with a single extra sheet and a single move row automation to move the row to the "working sheet" from the "intake sheet" ( <-- the one with the formula) to capture it as static. It still involves an extra sheet and a move row automation, but only one of each instead of multiple.

Jeff Reisman · 12/22/22

https://community.smartsheet.com/discussion/comment/355252#Comment_355252

Say you move the row with Project ID "ABC001", created today, off the main sheet to archive. Won't the next ABC row added to the main sheet today get assigned ABC001 as a Project ID? And if you don't move it to archive, when the second ABC row gets added today, both rows will have project ID ABC002.

And this part of the formula is always going to start over every day: =COUNTIFS([Project Type:]:[Project Type], @cell = [Project Type]@row, [Created Date]:[Created Date], @cell = [Created Date]@row)

So there's an ABC001 for Monday, an ABC001 for Tuesday, etc. Because you're saying "set this project ID to 'ABC' + the count of the rows where the project type is ABC that were created today."

I was on nitrous for over 3 hours at the dentist this morning, so maybe I'm missing something? 😵

Paul Newcome · 12/22/22

@Jeff Reisman I use a Copy Row instead of a Move Row.

And you are right that the formula is incorrect. I wasn't holding down the shift key when I hit the key before the =.

It was SUPPOSED to be

<=

not

,=

Slow fingers strike again... 😂

How to add a prefix to a project ID

Best Answers

Answers

Help Article Resources

Categories