Report a value of a child in a different column

Jessica White

I am trying to figure out how to report a value into the Parent row from one of the children. I want to be able to Collapse All tasks and be able to see the name of the Work Crew from the Task Name column populated in the Work Crew column.

The best I came up with so far is =IF(Activity@row = "Work", [Task Name]@row, "") which does highlight the correct value, but not in the Parent row.

I thought maybe an Index or Match would get me there and I've tried searching Community for help and all the formulas I try aren't quite getting me there. Any suggestions? Thank you for your help

Kelly Moore

Hello @Jessica White

Here is an approach but requires one helper column, formatted as a Text/Number column. I will call this helper column "Level". You can add it to the far right of your sheet and hide it, if desired. This helper column will identify the Parent rows from the sub parent rows (eg., McKamy-Little 4H Pad vs McKamy-Little 4H)

In the Level column, add this formula.

=COUNT(ANCESTORS())

You will see that the Parent rows all have values of zero and sub parents have a value of 1.

Then, in the [Work Crew] column, add this formula

=IF(Level@row = 0, JOIN(COLLECT(DESCENDANTS([Task Name]@row), DESCENDANTS(Activity@row), "Work")))

This formula can converted to a column formula as the IF statement filters between the Parent and Sub parents and grandchildren rows.

cheers,

Kelly

Kelly Moore

Hello @Jessica White

Here is an approach but requires one helper column, formatted as a Text/Number column. I will call this helper column "Level". You can add it to the far right of your sheet and hide it, if desired. This helper column will identify the Parent rows from the sub parent rows (eg., McKamy-Little 4H Pad vs McKamy-Little 4H)

In the Level column, add this formula.

=COUNT(ANCESTORS())

You will see that the Parent rows all have values of zero and sub parents have a value of 1.

Then, in the [Work Crew] column, add this formula

=IF(Level@row = 0, JOIN(COLLECT(DESCENDANTS([Task Name]@row), DESCENDANTS(Activity@row), "Work")))

This formula can converted to a column formula as the IF statement filters between the Parent and Sub parents and grandchildren rows.

cheers,

Kelly

Jessica White

Thank you, Kelly! This is exactly what I was trying to accomplish. Now I need to follow up on the formula pages to understand why!

Kelly Moore

Hey @Jessica White

The COLLECT formula is a way to gather data based on single or multiple criteria. It is a powerful function and is typically used in conjunction with something else. In this case I used JOIN, even though there was only one value to return. COLLECT has the syntax of Range of the data you're looking for, Range 1, criteria1, range2, criteria2, etc).

The DESCENDANTS function is one of the hierarchy functions and will locate all members of the same hierarchical family, beginning with the Level 0 parent. Note how this became the range in the formula - it allowed us to really filter the data that was needed.

If you are manually inserting the formula in your Level 0 parent, you don't need the IF statement that precedes the Collect formula. With the IF, the formula can be converted into a column formula.

If it's still not clear, let me know and I'll try to explain it better. Keep asking as many times as you need.

cheers,

Kelly