Automate formula

01/31/21
Accepted

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?



Best Answer

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted 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.

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted 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.

  • SharonRSharonR ✭✭✭✭✭

    Thank you this helps heaps.

  • Mark CronkMark 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.

Sign In or Register to comment.