If Children Contain then display cell on that row in Parent Cell
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

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 criteriarange2, 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
Answers

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 criteriarange2, 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

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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!