I need to be able to see children and grandchildren rows in a filter based on the parent row status.

BailBoss

I have a sheet that has parent, children and grandchildren rows. As you can see in the attached screenshot, each row level has a status. If I want to set up a filter to view only accounts in "default" status and assigned to "Maggie Spivey", for example, the filter would only show me the parent columns because I don't have the status updated on the children and grandchildren columns.

So I can either find a way to show all the children and grandchildren rows in the filter based on the parent status OR I can somehow find a formula that would update the children and grandchildren status' based on the parent row status. I do not want to have to update the status on each of the account levels manually each time a parent status is changed.

I have searched in this group and someone mentioned a helper row and then setting up an automation workflow but I don't want to do that because the workflows are horrible in this program. They take a while to actually trigger. My preference would be a way to create a filter where I just automatically see all parent/children/grandchildren based on the parent status. If that is not a possibility I would settle for a formula that updates the children and grandchildren rows to match the parent row.

Hope this make sense.

AravindGP

Hi @BailBoss

You can't get a formula to update the status of the parent row to child and grandchild rows. However, with a couple of helper columns.

1. Add a helper column to identify the Parent. The formula you would use is =IFERROR(INDEX(ANCESTORS([NAME]@row), 1), ""). This will write the value that is present in the NAME column of the parent in rows containing its children and grandchildren.
2. Add another helper column to write the status of the parent rows to its children and grandchildren. The formula you would use in this column would be =IF([Parent helper column]@row = "", Status@row, INDEX(Status:Status, MATCH([Parent helper column]@row, [NAME]:[NAME], 0))). This will write the status present in the parent row to all its children and grandchildren rows along with the parent row. You can use this as your filter criteria.

You might need to do something similar to get The ASSIGNED TO AGENT filled for children as well as that's going to be one of your filter criteria.