Filtering Parent Rows and Including All Child Rows

Options

I have a contact list where an organization name exists as the parent row and all contact names exist below as child rows. I want to be able to filter parent rows by any one of the other values on that row (i.e. program, contact owner(s), or partner type) and also include all child rows beneath even if they don't match that criteria.

Currently my workaround is to just copy the data to the child rows, but this looks messy and redundant.

Is there a way to include these child rows via formula if there is no native setting for this?

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @MarkF

    One approach is to add a helper column that places the Parent Org name on every Child row. Depending on how your sheet is set up, you may need to use the Hierarchy level instead. For instance if you have parents, grandparents ,child rows and just plain rows, you may need to use the hierarchy level. A screenshot would be helpful (or mock up to mask sensitive data). Assuming a row is only a parent or a child, try this in the helper column. This helper will give you a column that can be pulled into filters or reports.

    =IF(COUNT(CHILDREN()) > 0, [your primary column]@row, PARENT([your primary column]@row))

    Be sure to replace the name of the primary column placeholder in the formula with your real column name.

    Does this work for you?

    Kelly

  • cmore04
    cmore04 ✭✭✭✭
    Options

    Hi Kelly!

    I tried your formula but it doesn't work. It is saying Unparseable even when I change primary column name with the parent name.

    Does the primary column name have to be the actual sheet's primary column?

    Also, do I put this formula in the child rows or the parent row?


    Thank you!!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @cmore04

    Can you post a screen capture of your sheet, but block out sensitive data?

    You can replace the [your primary column]@row  reference with any column reference... the column you want to bring back the value for.

    =IF(COUNT(CHILDREN()) > 0, [Column Name]@row, PARENT([Column Name]@row))

    This would go in every single row, as a Column Formula.

    Cheers,

    Genevieve

  • cmore04
    cmore04 ✭✭✭✭
    Options

    Hi @Genevieve P. !

    Thank you for such a quick reply, it worked! But now I realized I actually need to show the ancestor name in the Phase column as well so I can filter out everything for one phase.

    For example in the formula now it only shows the direct parent name, but how do I include all parents in the phase column? I know using Join might help, but not sure?


    I'm hoping to put the grandparent name and parent name and child name in the phase column everytime:

    Grandparent - Parent1 - Child1


    Thank you so much for any help you can provide!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @cmore04

    JOIN is exactly what I'd suggest!

    Try:

    =IF(COUNT(ANCESTORS([Phase/Task]@row)) = 0, [Phase/Task]@row, JOIN(ANCESTORS([Phase/Task]@row), " - "))


    Cheers,

    Genevieve

  • cmore04
    cmore04 ✭✭✭✭
    Options

    It worked, Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!