Working on formula to return the value of the NEXT CHILD row in PARENT column

Jeana

I have a column called Project with a PARENT row called PROJECT 1. I am looking for a formula to use in another column (WHATS NEXT) that will return the value of the next CHILD row, until there are not more CHILDREN.

Appreciate any insight on this. Seems like it would be simple but I don't want to COUNT the values I want to return the CONTENT of the next row of the CHILDREN.

THANKS!!

Jeana

Paul Newcome

Ok. The only thing left to ask is what do you want displayed when ALL children are complete? That would go in the very last section of the below formula in between the quotes.

Here is the formula to return the next step:

=IFERROR(INDEX(CHILDREN([Project Column]@row), COUNTIFS(CHILDREN([Checkbox Column]@row), 1) + 1), "All Complete")

We start with an INDEX function to pull the data from the child rows of your Project Column.

To automatically determine the row number for the INDEX function we use a COUNTIFS to count how many boxes are checked and then add 1.

Finally we use the IFERROR for when all are complete because when all boxes are checked, the COUNTIFS will generate a row number larger than what is available which will in turn throw an error on the INDEX function.

David Joyeuse

Hi @Jeana

Could you elaborate a bit more, of share us screenshots of what you really intent to do.

If I understood it right, you want to display a value that is present in the Child row, but this same child row will change as project is evolving and activities in child rows are completed? Is that right?

Jeana

Here is a screen shot. I'm looking to put a formula in the column on the right to show the content for the Next row in the CHILDREN for the Virtual CHILDREN. Yes, the CHILDREN in this row will vary and there will be another 'set' of children below for another topic.

Hope that helps. Thanks!

David Joyeuse

OK @Jeana

I assume you have then some kind of % complete column on your sheet then. I had to sort this kind of problem some time ago. So this formula should do the trick:

=INDEX(CHILDREN([Action]@row),MATCH("Not Complete",CHILDREN([% Complete]@row),0))

Considering [Action] column is where you have the names that you want to display and [% Complete] column is the column you use to say if the children's task has been completed or not. Adapt this formula to what you already have in your sheet. You can also do the MATCH on some RYGB balls as well...

Up to you :)

Hope it helped!

Paul Newcome

How are you determining when to move to the next row?

Paul Newcome

How are you determining when to move to the next child row?

Jeana

Paul, there is a checkbox column (DONE) that will be used to indicate a step is completed and trigger an automated notification.

Jeana

@David Joyeuse I think I get the idea but I am using a checkbox to determine when the current step is done, not a % Complete column. So when DONE (checkbox) is check I would like the formula for this column to display the next STEP. It will also trigger an automation.

Does that make sense?

Jeana

Paul Newcome

Ok. The only thing left to ask is what do you want displayed when ALL children are complete? That would go in the very last section of the below formula in between the quotes.

Here is the formula to return the next step:

=IFERROR(INDEX(CHILDREN([Project Column]@row), COUNTIFS(CHILDREN([Checkbox Column]@row), 1) + 1), "All Complete")

We start with an INDEX function to pull the data from the child rows of your Project Column.

To automatically determine the row number for the INDEX function we use a COUNTIFS to count how many boxes are checked and then add 1.

Finally we use the IFERROR for when all are complete because when all boxes are checked, the COUNTIFS will generate a row number larger than what is available which will in turn throw an error on the INDEX function.

Jeana

@Paul Newcome THANK you so much. This is working just fine now and it's a formula that will help me in other areas as well. For each Parent now I can see what the next step in for that process. This is huge!

Thank you,

Jeana

Paul Newcome

Happy to help. 👍️