How to add a prefix to a project ID

Options

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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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!

  • Ddabbs
    Ddabbs ✭✭
    Options

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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!

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

    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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @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.

    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!

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

    @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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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!

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

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!