Is there a way to conditionally copy the value of one cell to another?

Jim Kiltie

I am trying to create a modified auto-numbering scheme where the numbering is based on a parent/child relationship. I think I am on the right track, but have hit a roadblock. My end result should be a calculated ID that is not overwritten when IDCalc changes.

I would like the value of IDCalc to populate in the ID column when IDNull is true. The problem is, if I add another child row the value of IDNum will increment for ALL child rows. Thus I cannot simply put a formula of =IDCalc@row in the ID column.

I was hoping I could do this with workflow, where I could change the value of ID in a newly added row with a formula, but apparently that is not possible. Maybe a future enhancement?

Any ideas how I can get ID to populate with a value then be left alone?

Further info:

LastNum = COUNT(CHILDREN(Primary@row))

NextNum = PARENT(LastNum@row)

Prefix = =IF(ISBLANK(PARENT([ID Prefix]@row)), [ID Prefix]@row + "-", PARENT([ID Prefix]@row) + "-")

IDNum = =IF(ISBLANK(NextNum@row), "00", IF(LEN(NextNum@row) = 1, "0" + (NextNum@row), NextNum@row))

IDCalc =IF(AND(NOT(ISBLANK(Prefix@row)), NOT(ISBLANK(IDNum@row))), JOIN(Prefix@row:IDNum@row), "")

IDNull = =IF(ISBLANK(ID@row), true, false)

jmyzk_cloudsmart_jp

Hi @Jim Kiltie

I would add the Row ID auto number column and the Parent Row ID helper columns for this solution.😀

How to fix your formula

Then, the LastNum formula becomes like this;

="0" + MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent ID]:[Parent ID], [Parent ID]@row), 0)

You want to use the zero padding numbering system like 01, 02, 03 instead of 1,2,3. In that case, it would be easier to do the padding here.

Then, the IDNum formula becomes like this.

=JOIN(ANCESTORS([Last Num]@row), "-") + IF(COUNT(ANCESTORS()) > 0, "-", "") + [Last Num]@row

Using ANCESTORS, you can cope with more than three level cases, like Parent, Child, and Grandchild.

Using IF(COUNT(ANCESTORS()) > 0, "-", "") removes the unnecessary delimiter at the top row.

You could use delimeter# instead of "-" if you use the Sheet Summary fields. (See at the bottom.)

More standard ways for auto-numbering

Please find more standard ways for auto-numbering on the right-hand side of the published demo sheet.

The difference between the fixed and dynamic is if you move the rows in a children group, the fixed one keeps the same Last Num or Self in my demo. In contrast, the dynamic one changes the Last Num depending on the relative position of the row in the same children group.

The formula for the "Self Dynamic" column is the same as for "LastNum." The "Self Fixed" uses the RANKEQ function instead of the MATCH function to get the absolute position of the row among the same children group.

https://app.smartsheet.com/b/publish?EQBCT=5c7112ac79614f75ac3757230c297e5e

You can specify the Prefix and delimiter in the Sheet Summary and call in the sheet cell formula as [filed name]#, delimiter#, for example.

By using the Sheet Summary, you can change those values dynamically.

jmyzk_cloudsmart_jp

Hi @Jim Kiltie

I would add the Row ID auto number column and the Parent Row ID helper columns for this solution.😀

How to fix your formula

Then, the LastNum formula becomes like this;

="0" + MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent ID]:[Parent ID], [Parent ID]@row), 0)

You want to use the zero padding numbering system like 01, 02, 03 instead of 1,2,3. In that case, it would be easier to do the padding here.

Then, the IDNum formula becomes like this.

=JOIN(ANCESTORS([Last Num]@row), "-") + IF(COUNT(ANCESTORS()) > 0, "-", "") + [Last Num]@row

Using ANCESTORS, you can cope with more than three level cases, like Parent, Child, and Grandchild.

Using IF(COUNT(ANCESTORS()) > 0, "-", "") removes the unnecessary delimiter at the top row.

You could use delimeter# instead of "-" if you use the Sheet Summary fields. (See at the bottom.)

More standard ways for auto-numbering

Please find more standard ways for auto-numbering on the right-hand side of the published demo sheet.

The difference between the fixed and dynamic is if you move the rows in a children group, the fixed one keeps the same Last Num or Self in my demo. In contrast, the dynamic one changes the Last Num depending on the relative position of the row in the same children group.

The formula for the "Self Dynamic" column is the same as for "LastNum." The "Self Fixed" uses the RANKEQ function instead of the MATCH function to get the absolute position of the row among the same children group.

https://app.smartsheet.com/b/publish?EQBCT=5c7112ac79614f75ac3757230c297e5e

You can specify the Prefix and delimiter in the Sheet Summary and call in the sheet cell formula as [filed name]#, delimiter#, for example.

By using the Sheet Summary, you can change those values dynamically.

Jim Kiltie

@jmyzk_cloudsmart_jp Thank you so much! This was extremely helpful and got me past my roadblock!