If Children Contain then display cell on that row in Parent Cell

Ross Kennedy

Have children() that vary in range, children rows can vary from 1 to ~30.

For each child that contains "Action" in column [Issue Category] display the cell Notes contents into parent row Notes cell.

Currently using static formula. Issue is every time a row is inserted, I have to manually add to the formula.

Current formula example:

=IF([Issue Category]196 = "Action",Date196 + ": " + Notes196 + SUBSTITUTE(Notes1, "-", ""), "") + IF([Issue Category]197 = "Action", Date197 + ": " + Notes197 + SUBSTITUTE(Notes1, "-", ""), "") + IF([Issue Category]198 = "Action", Date198 + ": " + Notes198 + SUBSTITUTE(Notes1, "-", ""), "") + IF([Issue Category]199 = "Action", Date199 + ": " + Notes199 + SUBSTITUTE(Notes1, "-", ""), "") + IF([Issue Category]200 = "Action", Date200 + ": " + Notes200 + SUBSTITUTE(Notes1, "-", ""), "")

Any help would be greatly appreciated. 

Kelly Moore

Hey

Give this a try

=JOIN(COLLECT(CHILDREN(), CHILDREN([Issues Category]@row), "Action"), " - ")

This JOINS everything that has been COLLECTED. It has the syntax of range - in this case the Children of our current column, the criteria range - in this case Children of [Issues Category] column, criteria = which =Action. If there were more criteria, we would continue adding criteria-range2, criteria2, etc.

I assumed this formula was going in the parent rows of the Notes column - if not, instead of CHILDREN() you'll have to specify CHILDREN(Notes@row).

I noticed in your current nested IF you are modifying the Notes using a SUBSTITUTION function. If you need this, I recommend adding a Substitution helper column using your substitution formula above, and using that column in the COLLECT formula instead of the unmodifi.

Unless you are entering Notes in the Child rows by formula, you will either have to manually add the Join/Collect formula to every Parent row or, add a different helper column. With this you could differentiate between the Child and Parent rows automatically by formula to eliminate having to keep copying the Join/Collect in every new Parent row

For example in this Helper Column, =IF(COUNT(CHILDREN(whatever your primary column name@row)>0, =JOIN(COLLECT(CHILDREN(), CHILDREN([Issues Category]@row), "Action"), " - ", Notes@row)

The above will look if Primary column@row has Children. If yes, which means its a Parent, the Join(Collect) will execute. If No, it will copy the Notes@row into the Helper Column Child row. If you did the Substitution, replace Notes@row with whatever you named your Substitution helper column.

Does this get you what you need?

Kelly

Kelly Moore

Hey

Give this a try

=JOIN(COLLECT(CHILDREN(), CHILDREN([Issues Category]@row), "Action"), " - ")

This JOINS everything that has been COLLECTED. It has the syntax of range - in this case the Children of our current column, the criteria range - in this case Children of [Issues Category] column, criteria = which =Action. If there were more criteria, we would continue adding criteria-range2, criteria2, etc.

I assumed this formula was going in the parent rows of the Notes column - if not, instead of CHILDREN() you'll have to specify CHILDREN(Notes@row).

I noticed in your current nested IF you are modifying the Notes using a SUBSTITUTION function. If you need this, I recommend adding a Substitution helper column using your substitution formula above, and using that column in the COLLECT formula instead of the unmodifi.

Unless you are entering Notes in the Child rows by formula, you will either have to manually add the Join/Collect formula to every Parent row or, add a different helper column. With this you could differentiate between the Child and Parent rows automatically by formula to eliminate having to keep copying the Join/Collect in every new Parent row

For example in this Helper Column, =IF(COUNT(CHILDREN(whatever your primary column name@row)>0, =JOIN(COLLECT(CHILDREN(), CHILDREN([Issues Category]@row), "Action"), " - ", Notes@row)

The above will look if Primary column@row has Children. If yes, which means its a Parent, the Join(Collect) will execute. If No, it will copy the Notes@row into the Helper Column Child row. If you did the Substitution, replace Notes@row with whatever you named your Substitution helper column.

Does this get you what you need?

Kelly

Ross Kennedy

Thanks KDM,

What you provided got me to where I was wanting. I very much appreciate your help.

I am using the Notes1 cell as the "Helper" for a "return" so that joined cell content doesn't run together. The Helper Cell is formatted as:

-

-

Final formula is =JOIN(COLLECT(CHILDREN(), CHILDREN([Issue Category]@row), "Action"), SUBSTITUTE(Notes1, "-", ""))

Result in Parent Row Notes cell is something like:

Action 1

Action 2

Action 3

Where Action 1 is from Notes on child row 3, Action 2 from child row 6 and Action 3 from child row 201.

Thanks Again

Ross