Using a column formula to populate child rows without changing data in parent row of same column
I have a sheet where I am tracking meeting engagements with stakeholders. The Parent Row data is all manually entered and I want the data to autopopulate the children rows of some of the columns (e.g., Sector). I'd like to have a column formula that does this without affecting the data in the parent rows.
I have my parent rows identified with a Level column using a formula - parent rows are 1 and children rows are 2.
I set up some workflow automation so that when someone manually chooses a sector in a parent row, it will automatically fill in the same text in the helperSector column of that same row (e.g., choose Sector1 and it will return Sector1 in the helper column).
I then went to set the following formula as a column formula in my Sector Column: =IF(COUNT(CHILDREN()) = 0, IF(Level@row = 2, INDEX(ANCESTORS(helperSector@row), 1)))
It works fine when entered as just a cell formula, but as a column formula it removes all the data in the parent rows and only shows the data in the children rows (see next screenshot showing the data only populating the child rows).
I would really like to have a column formula to help automate the data entry for individual engagements. What piece am I missing to ensure the data in the parent cell doesn't disappear?
Help Article Resources
Check out the Formula Handbook template!