How to add a prefix to a project ID
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!
Best Answers
-
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))
-
@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:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
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))
-
@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:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Thank you for the response! Yes this is exactly what I was looking for.
-
Happy to help!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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.
-
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? 😵
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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... 😂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!