Sequential project number based on team and product name?

Hello Smart community,

Trying to achieve what seems many are also working on: sequential project numbers.

Below, the team is a drop down with options, the product name is manual and the result is what's below.

Trying to achieve the following:

UXR00 ending with a sequential number, e.g. 001... n

If same team, increase 1, e.g.

UXR002-GT-RTW

UXR003-GT-RTW

If different team, look for last number and increase thereafter, e.g.

UXR001-EE-POS

UXR002-EE-PPT

Please help?

Thanks


Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey @GMartin

    Create another column for the Number and insert the formula below into it. Unfortunately, it can't be a column formula because that's not supported for whatever reason.

    =IF(AND(ISBLANK([Product Name]@row), ISBLANK(Team@row)), "", COUNT(COLLECT([Product Name]$1:[Product Name]@row, Team$1:Team@row, Team@row, [Product Name]$1:[Product Name]@row, [Product Name]@row)))
    

    Then in the Project Number column use this formula. This one can be a column formula.

    =IF(Number@row = "", "", "UXR" + (RIGHT(1000 + Number@row, 3)) + "-" + Team@row + "-" + [Product Name]@row)
    

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey @GMartin

    Create another column for the Number and insert the formula below into it. Unfortunately, it can't be a column formula because that's not supported for whatever reason.

    =IF(AND(ISBLANK([Product Name]@row), ISBLANK(Team@row)), "", COUNT(COLLECT([Product Name]$1:[Product Name]@row, Team$1:Team@row, Team@row, [Product Name]$1:[Product Name]@row, [Product Name]@row)))
    

    Then in the Project Number column use this formula. This one can be a column formula.

    =IF(Number@row = "", "", "UXR" + (RIGHT(1000 + Number@row, 3)) + "-" + Team@row + "-" + [Product Name]@row)
    
  • Worked perfectly fine! Thank you SO much for the quick reply.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!