Automate formula

SharonR
SharonR ✭✭✭✭✭
in Formulas and Functions

Is there a formula that can do the picture below.... the Blue unlined column is provided in the form request, The Black underline column needs to recognise the contract id, then find the last V number allocated and allocated the next number.... so that the Red underlined column, when joined creates the variation ID number.... Any suggestions?


123456789.PNG


· Share on FacebookShare on Twitter

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @SharonR ,

    This is a hard one. You have to avoid creating a circular reference. To do that, I recommend adding an auto number system column to record the order rows are added. You'll use the auto number column [row ID] to determine which rows existed prior to the new row.

    Your formula for Variation would be:

    =IF(ISBLANK([Contract ID]@row), "", IF(COUNTIFS([Contract ID]:[Contract ID], [Contract ID]@row, [Row ID]:[Row ID], <[Row ID]@row) = 0, "V00", IF(COUNTIFS([Contract ID]:[Contract ID], [Contract ID]@row, [Row ID]:[Row ID], <[Row ID]@row) < 10, "V0" + COUNTIFS([Contract ID]:[Contract ID], [Contract ID]@row, [Row ID]:[Row ID], <[Row ID]@row), "V" + (COUNTIFS([Contract ID]:[Contract ID], [Contract ID]@row, [Row ID]:[Row ID], <[Row ID]@row)))))

    This will make your contract variation numbering dynamic. If a row is deleted all will be renumbered. That could cause you problems.

    Work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @SharonR ,

    This is a hard one. You have to avoid creating a circular reference. To do that, I recommend adding an auto number system column to record the order rows are added. You'll use the auto number column [row ID] to determine which rows existed prior to the new row.

    Your formula for Variation would be:

    =IF(ISBLANK([Contract ID]@row), "", IF(COUNTIFS([Contract ID]:[Contract ID], [Contract ID]@row, [Row ID]:[Row ID], <[Row ID]@row) = 0, "V00", IF(COUNTIFS([Contract ID]:[Contract ID], [Contract ID]@row, [Row ID]:[Row ID], <[Row ID]@row) < 10, "V0" + COUNTIFS([Contract ID]:[Contract ID], [Contract ID]@row, [Row ID]:[Row ID], <[Row ID]@row), "V" + (COUNTIFS([Contract ID]:[Contract ID], [Contract ID]@row, [Row ID]:[Row ID], <[Row ID]@row)))))

    This will make your contract variation numbering dynamic. If a row is deleted all will be renumbered. That could cause you problems.

    Work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter
  • SharonR
    SharonR ✭✭✭✭✭

    Thank you this helps heaps.

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Thank you for contributing to the community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!