Automate formula

SharonR

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?

Mark Cronk

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

Mark Cronk

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

SharonR

Thank you this helps heaps.

Mark Cronk

Thank you for contributing to the community.

Mark