Is there a way to conditionally copy the value of one cell to another?
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)
Best Answer
-
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.
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.
Answers
-
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.
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 Thank you so much! This was extremely helpful and got me past my roadblock!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!