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?
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)
Help Article Resources
Check out the Formula Handbook template!