Keeping Parent with Children

This discussion was created from comments split from: Keeping Parent with Children when Filtering.

Answers

  • @Paul Newcome @Andrée Starå thanks for your help. Im apply all your advices about this but Its no worked in my case.

    I have a project sheet, a secondary sheet with an automation for fill the project sheet.

    Project sheet

    Secundary sheet for fill automatically the project sheet with 1 parent row and 5 children rows (I would like the parent row to be copied to the project sheet along with the formulas so that these formulas could work on the project sheet without hte needs to copy all the rows on it after.

    formulas that I use on helper (secundary) sheet

    =INDEX(Campoproyecto:Campoproyecto, MATCH(MAX(autonumber:autonumber), autonumber:autonumber, 0))


    my last question is a filters question

    I want to know all the "Proyecto por iniciar" status (for example), when I make the filter for show all the "Proyecto por iniciar" status, the children row not appear with "Proyecto por iniciar" parent row. I need to see all the children rows with filtered parent rows also when I make the filter.

    I follow the advice of the community but it not work

    formula that I use on work sheet

    =IF(COUNT(CHILDREN(Project@row)) = 0, PARENT(Status@row), Status@row)

    when I make the filter show the parents row without child row.

    sorry about the long of this post, I hope that someone help me,

    In advance thanks for any help


    Johann

    Panamá

  • Brian_Richardson
    Brian_Richardson Overachievers

    Hi Johann. Some answers:

    • You can use the Copy Row automation to copy the parent and it's children. If you trigger the automation to copy the parent row, the children will be copied too. But if you trigger the children to be copied, the parent will not be copied with them. So, find a way to have a trigger only for parent row copies.
    • You cannot copy formulas, it doesn't work. The value from the formula is what copies. I also tried triggering an automation on your primary sheet to replace the cell with a formula, but that doesn't work either as it replaces the cell with a text version of the formula. However, what you can do is leverage a column formula, setup a column with a formula that looks at the "Level" of the row and does your Index/Match if it's a child row. I often use a "Level" helper column with the formula =count(Ancestors([Primary Column]@row)) to find out what level (0 as the top level, 1 as a single indent, etc) something is.
    • You need to filter for Helper Column, not Status. Helper Column shows the status applied to the children.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!