Auto Number Formula Help - Add Project Code as Prefix

Hello,

Is there a way to create an auto-number system to show [Project Code]-P-[AutoNum] where the auto number references the project code in the same row?

Here's an example to illustrate:

But when I try to add this prefix system to the Auto Number column properties…

[Project Code]-P- types it literally… "[Project Code]-P-"

=[Project Code]@row (or other attempts to type a formula in the prefix area) are also literal.

{{Project Code}}-P- (which works in the automation message dialog) is an invalid format.

And I can't seem to type a formula on the grid view when that column type is selected.

I'm still new to Smartsheet, so maybe there's a way to do this, but I can't figure it out.

I'd appreciate any help or recommendations for a workaround!

Find more posts tagged with

Accepted answers

Protonsponge

Hello @S.Stone

One way of achieving this would be to set the Auto Number column to give you the "-P-001" portion of the project name. You can then combine the detail in your [Project Code] column to give you the full project name/number.

Auto Number Column:-

Project Number Column

=[Project Code]@row + [Auto Number]@row

I hope that is helpful to you in some way,

Protonsponge

All comments

Protonsponge

Hello @S.Stone

Auto Number Column:-

Project Number Column

=[Project Code]@row + [Auto Number]@row

I hope that is helpful to you in some way,

Protonsponge

Hollie Green

Have your auto number column only do the number then you can create the column to show what you want using the below formula

=[Project Code]@row + "-P-" + IF(COUNTIFS([Project Code]:[Project Code], [Project Code]@row, [Auto Number]:[Auto Number], <=[Auto Number]@row) < 10, "0" + "0" + (COUNTIFS([Project Code]:[Project Code], [Project Code]@row, [Auto Number]:[Auto Number], <=[Auto Number]@row)), IF(COUNTIFS([Project Code]:[Project Code], [Project Code]@row, [Auto Number]:[Auto Number], <=[Auto Number]@row) < 100, "0" + (COUNTIFS([Project Code]:[Project Code], [Project Code]@row, [Auto Number]:[Auto Number], <=[Auto Number]@row)), (COUNTIFS([Project Code]:[Project Code], [Project Code]@row, [Auto Number]:[Auto Number], <=[Auto Number]@row))))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.