Grouping Rows

Options

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 Admin
    Answer ✓
    Options

    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!

Answers

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

    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Have you tried collapsing the parent rows and then sorting?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

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

  • RingJake
    RingJake ✭✭✭✭✭
    Options

    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.


  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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?

  • RingJake
    RingJake ✭✭✭✭✭
    Options

    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 Admin
    Answer ✓
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!