Create a formula that auto numbers

Options
Marcy Morris
Marcy Morris ✭✭✭
edited 11/07/22 in Formulas and Functions

Hi I'm in need of a formula to execute the following:

I have a Project tracker that has auto numbering that I now need to change. When a new Project is added to the worksheet containing a Project ID with the letters "P" or "HR" I need to retain that Project ID instead of the next auto number Project ID. When a project is added to the worksheet that does not contain a Project ID then I need the auto numbering to continue from the last auto number. I'm in dire need of a solution.

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Marcy Morris

    The following formula would work (in Output column):

    =IF(OR(CONTAINS("P", Project@row), CONTAINS("HR", Project@row)), Project@row, Autonumber@row)

    This assumes that your projects with "P" or "HR" don't have longer chains containing these characters.

    You can set the Autonumber column to start from a later value if you're pasting in data that already exists.

    For visibility you could hide the Autonumber to have only the Output column showing.

    Hope this helps - if you've any questions just ask.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Marcy Morris

    The following formula would work (in Output column):

    =IF(OR(CONTAINS("P", Project@row), CONTAINS("HR", Project@row)), Project@row, Autonumber@row)

    This assumes that your projects with "P" or "HR" don't have longer chains containing these characters.

    You can set the Autonumber column to start from a later value if you're pasting in data that already exists.

    For visibility you could hide the Autonumber to have only the Output column showing.

    Hope this helps - if you've any questions just ask.

  • Marcy Morris
    Options

    Hi Nick

    Thanks for your speedy response I was so thrilled it worked that I completely forgot to thank you.

    Kind regards

    Marcy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!