Auto populating Status of a child row to duplicate Parent Row

MelPen643

I have a project I'm working on where I want to auto generate steps based on the status in two columns. I figured out the formula for that.

My issue right now is that without me manually entering/copying the status (I will normally not be in this sheet after it's developed) or having the user enter the status for all 6 rows for the same account (there will be 5-10 accounts on a sheet), I cannot figure out how to get a status in the children rows for my formulas to use as reference.

The formula I'm using is =INDEX(COLLECT({Auto Populate Action Steps Range 4}, {Auto Populate Action Steps Range 2}, [Child Customer Status]@row, {Auto Populate Action Steps Range 3}, Customer@row), 1) so I need something in the "Child Customer Status" column.

I've included a copy of my sheet and the sheet that is referenced in the formula.

The invalid value is only there because the child customer status column is blank. I manually entered a few in the top 5 boxes to make sure the formula worked.

Michael Culley

Did you want them to stay blank if nothing is entered?

If so you can just add an iferror statement to your formula.

=IFERROR(INDEX(COLLECT({Auto Populate Action Steps Range 4}, {Auto Populate Action Steps Range 2}, [Child Customer Status]@row, {Auto Populate Action Steps Range 3}, Customer@row), 1),"")

MelPen643

I'm not too worried about the second formula to populate the action leaving a blank if there's nothing to fill as they will be entering an account when needed and they need the steps as soon as they enter it.

I'm more addressing the need to take the "existing", "developing", etc status from the parent row down to the children rows. Although, i think it might be children rows down to grandchildren rows.