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

first

This question was posted by Jim Kiltie on the Smartsheet forums.He is trying to create a modified automatic numbering scheme based on parentage, but is facing a particular problem.The final result should be a calculated ID that is not overwritten when IDCalc is changed.Specifically, I want to embed the value of IDCalc into the ID column when IDNull is true.However, when I add a new child row, the value of IDNum increases for all child rows.So you can't simply put the expression =IDCalc@row on the ID column.Jim thought he might be able to accomplish this with a workflow, but it doesn't seem possible.Do you have any ideas or solutions to this problem?

Question summary

Jim Kiltie is using Smartsheet to create an automatic numbering scheme based on parentage.He wants to embed the value of IDCalc into the ID column when IDNull is true, but he has a problem.Adding a new child row causes the value of IDNum to increase for all child rows.

Answer summary

jmyzk_cloudsmart_jp suggests adding a Row ID automatic number column and a Parent Row ID helper column as a solution to this problem.It also explains how to modify the formula to implement the numbering scheme you want.By following his suggestions, you will be able to solve the problem.

翻 訳

https://community.smartsheet.com/discussion/comment/405939#Comment_405939

Jim Quilty ✭

11/18/23

I'm trying to create a modified auto-numbering scheme where numbering is based on parent-child relationships.I think I'm on the right path, but I'm hitting a roadblock.The final result should be a calculated ID that is not overwritten even if IDCalc changes.

image.png

If IDNull is true, I want to populate the ID column with the value from IDCalc.The problem is that when I add another child row, the IDNum value of all child rows increases.Therefore, you cannot simply enter the formula =IDCalc@row in the ID column.

I was hoping that I could do this using a workflow and use a formula to change the value of the newly added row's ID, but apparently that's not possible.Perhaps a future enhancement?

How can I enter a value in the ID and leave it alone?

Detailed information:

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)

tag: 

best answer

  • jmyzk_cloudsmart_jp ✭✭✭✭✭✭
  • 11/21/23 Answer ✓
  • Hello @Jim Kiltie
    This solution adds a Row ID autonumber column and a Parent Row ID helper column. 😀
    How to fix formulas
    after that,LastNum The formula looks like this:
    =“0” + MATCH([row ID]@row, COLLECT([row ID]:[row ID], [parent ID]:[parent ID], [parent ID]@row), 0 )
    Suppose you want to use a zero-padded numbering system like 1, 2, 3 instead of 01, 02, 03.In that case it's easier to do the padding here.
    after that, IDNum The formula looks like this:
    =JOIN(ANCESTORS([last number]@row), “-“) + IF(COUNT(ANCESTORS()) > 0, “-” ;, “”) + [last number]@row
    ANCESTORS allows you to handle cases at three or more levels, such as parent, child, and grandchild.
    Using IF(COUNT(ANCESTORS()) > 0, “-“, “”) removes unnecessary delimiters in the first row.
    You can also use the delimiter # instead of '-'.When using sheet summary fields. (See bottom.)
    A more standard method for automatic numbering
    Find a more standard method of automatic numbering on the right side of the published demo sheet.
    The difference between fixed and dynamic is that fixed maintains the same Last Num or Self in my demo if you move a row within a child group.In contrast, the dynamic one changes the Last Num depending on the relative position of the rows within the same child group.
    The formula for "self-dynamic" is:Column is the same as "LastNum". Use his RANKEQ function instead of the "self-fixing" MATCH function to get the absolute position of rows within the same child group.
  • smart seat
  • https://app.smartsheet.com/b/publish?EQBCT=5c7112ac79614f75ac3757230c297e5e
  • image.png

  • image.png
  • You can specify the prefix and delimiter in the sheet overview and call it in a formula in a sheet cell, for example, [filename]#, delimiter#.
    You can change these values ​​dynamically using the sheet overview.

answer

  • jmyzk_cloudsmart_jp ✭✭✭✭✭✭
  • 11/21/23 Answer ✓
  • Hello @Jim Kiltie
    This solution adds a Row ID autonumber column and a Parent Row ID helper column. 😀
    How to fix formulas
    after that,LastNum The formula looks like this:
    =“0” + MATCH([row ID]@row, COLLECT([row ID]:[row ID], [parent ID]:[parent ID], [parent ID]@row), 0 )
    Suppose you want to use a zero-padded numbering system like 1, 2, 3 instead of 01, 02, 03.In that case it's easier to do the padding here.
    after that, IDNum The formula looks like this:
    =JOIN(ANCESTORS([last number]@row), “-“) + IF(COUNT(ANCESTORS()) > 0, “-” ;, “”) + [last number]@row
    ANCESTORS allows you to handle cases at three or more levels, such as parent, child, and grandchild.
    Using IF(COUNT(ANCESTORS()) > 0, “-“, “”) removes unnecessary delimiters in the first row.
    You can also use the delimiter # instead of '-'.When using sheet summary fields. (See bottom.)
    A more standard method for automatic numbering
    Find a more standard method of automatic numbering on the right side of the published demo sheet.
    The difference between fixed and dynamic is that fixed maintains the same Last Num or Self in my demo if you move a row within a child group.In contrast, the dynamic one changes the Last Num depending on the relative position of the rows within the same child group.
    The formula for "self-dynamic" is:Column is the same as "LastNum". Use his RANKEQ function instead of the "self-fixing" MATCH function to get the absolute position of rows within the same child group.
  • smart seat
  • https://app.smartsheet.com/b/publish?EQBCT=5c7112ac79614f75ac3757230c297e5e
  • image.png

  • image.png
  • You can specify the prefix and delimiter in the sheet overview and call it in a formula in a sheet cell, for example, [filename]#, delimiter#.
    You can change these values ​​dynamically using the sheet overview.
  • Jim Quilty ✭
  • 11/21/23
  • @jmyzk_cloudsmart_jp thank you.This was very helpful and helped me overcome obstacles.

Summary

This post provides the solution to Jim Kiltie's question on the Smartsheet forums.The problem was implementing an automatic numbering scheme based on parent-child relationships.As a solution, adding a Row ID auto-number column and a Parent Row ID helper column was suggested, along with instructions on how to modify the formula.This resolved my issue and provided useful information for using Smartsheet effectively.

Leave a comment