Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  1. Build a community to enable the academic mission
  2. Create a thriving, fair and diverse University
  3. Develop next generation leaders
  4. Strengthen and support talent experience
  5. 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?

Tags:

Best Answer

  • Employee
    Answer ✓

    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

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • ✭✭
    edited 01/29/25

    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!

  • 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

  • Employee
    Answer ✓

    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

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6