Using a column formula to populate child rows without changing data in parent row of same column

DesireeD

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?

Thank you!

Kelsey Seitter

Hi @Desiree,

Hope I understand this correctly...in the "Sector" column, if a parent row, you'd like to show the value in the "helperSector" column, and if a child row, you'd like to see the value from the "helperSector" column for the parent row?

=IF(COUNT(CHILDREN()) = 0, PARENT([Sector1]@row), helpersector@row)

I believe this should work, but would need some adjustments if you plan to add hierarchy levels.

=IF(COUNT(CHILDREN()) = 0, PARENT([Sector1]@row), INDEX(ANCESTORS(helperSector@row),1))

Hard to verify formulas without completely rebuilding the sheet, so I hope this helps.

DesireeD

Hi @Kelsey Seitter,

In the "Sector" column, I want the parent row to maintain the data that someone manually inputs, but I want the child rows in the "Sector" column to pull the "helperSector" column data from the parent row. (The helper column has a workflow that automatically changes parent row cell data to match the value that was manually selected in the Sector Column.)

I used your second formula, but swapped "Sector1" for "helperSector"

It works as a cell formula, but not as a column formula for the Sector Column. Second image here shows that it correctly changes the child cell but also changes the parent cells to #INVALID VALUE (which I want the parent cells to maintain their original data).

I know it is this "PARENT([Sector1]@row)" piece of the formula that is missing, but I can't seem to nail it.

DesireeD

I think I figured it out!

=IF(COUNT(CHILDREN(Sector@row)) = 0, PARENT(helperSector@row), helperSector@row)

works as a column formula in my Sector column without changing the data in my parent rows. I found this thread in the forum that helped me get there: https://community.smartsheet.com/discussion/82155/populating-parent-and-child-rows-with-the-same-data