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

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

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

    @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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!