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?

Screen Shot 2021-08-16 at 1.37.09 PM.png

Find more posts tagged with

Formulas

functionality

Groups and Group Management

Accepted answers

Genevieve P.

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!

All comments

RingJake

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.

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:

Screen Shot 2021-08-17 at 12.49.58 PM.png

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

Have you tried collapsing the parent rows and then sorting?

Genevieve P.

https://community.smartsheet.com/discussion/comment/301708#Comment_301708

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

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.

Screen Shot 2021-08-17 at 9.08.43 AM.png

Genevieve P.

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

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.

Hi @RingJake

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.