Automated project ID using 2 column variables

Hi!

I'm trying to create a formula or automate the generation of a project ID but it needs to include 2 separate variables where the information are in 2 separate columns.

Column 1: Has 1 of 5 pillars with the pillar number and text. Below

Build a community to enable the academic mission
Create a thriving, fair and diverse University
Develop next generation leaders
Strengthen and support talent experience
Embed exemplary and compliant practices

Column 2: Has the team we want to assign to eg.

CPO - Employment Compliance Directorate
CPO - HR Business Partners
CPO - Office of Indigenous Employment & Development (OIED)
CPO - Organisational Development
CPO - Workplace Relations & Integrity
OCPO
Workforce Strategy & Planning
HR Services

Required END RESULT: P<pillar number><3 digit job number>-<team assigned>

So it would look like P10003-OD

I was told to create a new column for the 2 columns so it creates P1 and then OD THEN concatenate but the 2 additional columns are more manual work. Is there a more automated way to get this automated job number?

Find more posts tagged with

Automations

Accepted answers

Georgie

Hi @Carmen H,

I’ve tested the formulas that @L J provided and they’re working for me - thanks for the great advice, L J!

The formulas will work with dropdown columns and the order of the columns in your sheet won’t affect the formulas. However, if your column names are different to those in the formulas, even including or excluding a space, this could be causing the UNPARSEABLE error.

If you can’t work out what the issue is, could you provide a screenshot of your sheet showing all the column headers that are referenced by the formula, and showing the full formula in the cell as if you’re about to edit it? That should help us work out what’s causing the error.

Thanks,

Georgie

All comments

L J

I would suggest creating a new column (e.g. "Unique #") where you can use the Auto-number column type to generate the unique #### number for the project. Then, your Project ID column can build the value based on the Pillar, Unique # and Team fields.

Unique # column with 0000 numerical places in the display:

For this example, your Pillar column (Dropdown) would look like this:

For this example, your Team column would look like this: (Notice that it has the Team "Code" to the left of the | symbol, so that you can just include a short team code in your project ID).

Then, your Project ID column could use a column formula to combine all the information:
="P" + LEFT(Pillar@row, 1) + [Unique #]@row + "-" + LEFT(Team@row, FIND(" | ", Team@row)-1)

If you Pillars may grow to 10 or more, you may want to adjust the formula to accommodate 2+ digit numbers:
="P" + LEFT(Pillar@row, FIND(". ", Pillar@row) - 1) + [Unique #]@row + "-" + LEFT(Team@row, FIND(" | ", Team@row) - 1)

Hope this helps!

Carmen H

https://community.smartsheet.com/discussion/comment/470539#Comment_470539

Hi! Appreciate your feedback.

My cell is coming back with #UNPARSEABLE. Is this because the values must be text? I have the pillar and Team columns as drop down fields.

My columns are also not ordered the same way, does that matter?

Thank you in advance

Georgie

Hi @Carmen H,

I’ve tested the formulas that @L J provided and they’re working for me - thanks for the great advice, L J!

Thanks,

Georgie

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.