Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Filtering Parent Rows and Including All Child Rows

    ✭✭
    in Formulas and Functions

    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?

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Community Champion

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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!!

      · Share on FacebookShare on Twitter
    • Employee

      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

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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?

      image.png


      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!

      · Share on FacebookShare on Twitter
    • Employee

      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

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      It worked, Thank you so much!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions