Grouping Rows

Hello,


I have created a smartsheet update tracker, in the format shown in the screenshot below. The separate teams provide their updates in the nested row of the spreadsheet. There are also many formulas on the sheet.


I would like to sort these rows from time to time so they can display in progress projects at the top, (especially as completed forms will add to the spreadsheet), however none of the functions keep after they are sorted. Is there a way to resolve this.


Grouping rows I would assume would be a solution or locking rows in a formula?



Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @RingJake

    It looks like you already have the Parent name (the Partner Name) in a column that has no data in the Child Rows below, so you wouldn't need a helper column for this field. You would only need the helper to extract the Status. Then you can Sort by Parent and then Status.

    You could also collapse all rows and then drag the rows to your preferred order, yes!

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • RingJake
    RingJake ✭✭✭✭✭
    edited 08/16/21

    What it looks like I really need is the ability to sort only by the Parent rows, while keeping the child rows in the same order.

  • Hi @RingJake

    If you want to sort the Parent rows but keep the Child rows in the same order, you'll need to sort by a value that only appears in a Parent row.

    The way I would do this is to add a helper column into your sheet that is specific for Sorting (you can hide this column once it's created). Then I'd use a column formula to keep the Child Rows blank, but repeat the value you want to Sort By (ex. the % Complete for that Parent or the Status).


    Here's the formula I used:

    =IF(COUNT(CHILDREN([% Complete]@row)) > 0, [% Complete]@row, "")


    If there are Children associated with this row, then display the % Complete. Otherwise, display a blank cell. It looks like this in my sheet:


    Since the child rows are blank in the helper column, the Sort will ignore those and only rearrange the Parents (bringing the children with them).

    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Have you tried collapsing the parent rows and then sorting?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I quickly tested this - collapsing the rows will still sort the child rows by that value as well (keeping them under the Parent though).

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • RingJake
    RingJake ✭✭✭✭✭

    Hey @GenDocStudio and @Paul Newcome,


    Thank you both for your quick replies. I do have a helper cell already that checks for parents, however when using the sort option at any time it does not keep the formula around the desired child cells since they are moving as well.


    What I've found works best is dragging the parent cells in the order I want them, but unfortunately this is quite manual.


  • Hi @RingJake

    The helper column would need to pull the data you want to Sort by, not just identify the Parent row. Does that make sense?

    For example, you'd need to pull the % Complete for each Parent, or the Status ("In Progress"). That way you can use it as criteria for the Sort. The checkbox will be the same value for every Parent so the Sort won't know what order you want the Parents to be in.

    How do you want to order your sheet, by % Complete?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • RingJake
    RingJake ✭✭✭✭✭

    Hi @Genevieve P., I am looking to sort the sheet by Parent, then status, then alphabetical.


    But I think without adding three more helper cells I'll just have to do this by dragging cells around?

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @RingJake

    It looks like you already have the Parent name (the Partner Name) in a column that has no data in the Child Rows below, so you wouldn't need a helper column for this field. You would only need the helper to extract the Status. Then you can Sort by Parent and then Status.

    You could also collapse all rows and then drag the rows to your preferred order, yes!

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!