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

01/14/21
Accepted

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. 

Best Answer

  • KDMKDM ✭✭✭✭✭
    Accepted Answer

    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([email protected]).

    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 [email protected])>0, =JOIN(COLLECT(CHILDREN(), CHILDREN([Issues Category]@row), "Action"), " - ", [email protected])

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

    Does this get you what you need?

    Kelly

Answers

  • edited 01/14/21

    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

Sign In or Register to comment.