Transferring largest value of children's row

I am trying to report the document review status. As you can see in the picture, I'd like to report the latest version of the comment sheet of every document.

In the picture, for each topic (there are two topics: Gardening and Cooking) there will be many documents. In this example, Gardening has 3 documents : Apple, Blueberry, and Grape.

For each document, there will be many versions as well as the comment sheet version. There isn't any fix pattern on the sequences.

My objective is to report the latest version of comment sheet for every document in the "Latest comment version" column as shown in the picture. When the comment sheet is not yet issued, I want to write R00.

I am still new to smartsheet and kinda lost here. I hope you can help me with this. Thanks.

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @TYL

It looks like it's working, just for the wrong level!

In your example image, the last child row from Gardening has a blank cell, there is no "R" number. If you type something in the "Grape Version 1" row under the column "Comment Version", that text will appear in the "Gardening" row.

However thanks for clarifying that you're looking to bring this into the second level, not the top level. In this instance we just need to change out the IF part of the formula.

Our statement is looking for the top level, which is Blank. Instead, change the helper Parent Names column to a Multi Select column, and adjust the JOIN formula to create separate values:

=JOIN(ANCESTORS([Task Name]@row), CHAR(10))

Then we can count how many values appear. If there's only 1 value, it means this is the second-level row, one down from the top level.

=IF(COUNTM([Parent Names]@row) = 1,

And then since we changed it to Multi Select, we can see if that cell HAS a selection (versus Contains):

=IF(COUNTM([Parent Names]@row) = 1, INDEX([Comment Version]:[Comment Version], MAX(COLLECT(Row:Row, [Parent Names]:[Parent Names], HAS(@cell, [Task Name]@row)))))

Keep in mind that your Grape row will still be blank, since its child does not have any data. Let me know if you'd like to see screen captures!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @TYL

We can use the MAX function to find either the highest number or the latest date. However it looks like your "Comment Version" is a mixture of numbers and letters, and that you're looking for the last row. If that's correct, we will need to add a few helper columns to your sheet to help the formula calculate.

1) Auto Number Column

What I would do in this instance is add an Auto-Number column to your sheet (which you can hide). Let's call this "Auto".

2) Row Number Column

Then add a Row Number column to your sheet (Text/Number column) with a formula so that you can always display the row number in a cell.

=MATCH(Auto@row, Auto:Auto)

This is what we'll use for the MAX function to find the highest number in your grouping. See: MATCH Function

Screen Shot 2022-07-15 at 6.02.14 PM.png

3) Parent Names Column

Next, I would add a third (and final) helper column to bring the top Parent Name into each of the Child rows:

=JOIN(ANCESTORS([Task Name]@row), ", ")

Screen Shot 2022-07-15 at 6.14.12 PM.png

This will allow us to filter down your grouping because the Parent name will be in every level below. See: JOIN Function, ANCESTORS Function

4) Final Formula

Now that we have all the data we need in the grid of the sheet, we can set up a formula in your Latest Comment Version column.

This formula will first check if the Parent Names cell for the row is blank. If it is, this means the current row is the TOP level, the one where we want to bring back data. If it isn't, then this row is a Child row (of any level) so the formula will be blank.

=IF([Parent Names]@row = "", ...

Then to bring back the Comment, we can use an INDEX(...MAX( formula to read through the Comment Version Column with the INDEX, but use the MAX data in the Row column to find the latest row, filtering by if those rows contain the correct Parent name:

=IF([Parent Names]@row = "", INDEX([Comment Version]:[Comment Version], MAX(COLLECT(Row:Row, [Parent Names]:[Parent Names], CONTAINS([Task Name]@row, @cell)))))

Screen Shot 2022-07-15 at 6.18.18 PM.png

Once you have all of this set up, and set to Column Formulas, you can hide all three helper columns in your sheet and the formula will still run.

Let me know if this works for what you're looking to do!

Cheers,

Genevieve

TYL

Hi Genevieve,

Thank you very much for explaining everything in detail. However, after following your guide, I still somehow have some difficulties.

First of all, I want to show you what my results are after following your guide:

The column "Latest Comment Version" only show result for Row 13, while Row 1 is still empty.

However, this not actually what I am looking to do. What I want to capture (based on the example you provide), is the latest comment version of each "Parent", not the "Task". Please refer to the snapshot below. In this example, I expect that each parent should capture the latest comment version.

Do you think there is a way to do this? Thank you very much for your help.

Genevieve P.

Hi @TYL

It looks like it's working, just for the wrong level!

However thanks for clarifying that you're looking to bring this into the second level, not the top level. In this instance we just need to change out the IF part of the formula.

Our statement is looking for the top level, which is Blank. Instead, change the helper Parent Names column to a Multi Select column, and adjust the JOIN formula to create separate values:

=JOIN(ANCESTORS([Task Name]@row), CHAR(10))

Then we can count how many values appear. If there's only 1 value, it means this is the second-level row, one down from the top level.

=IF(COUNTM([Parent Names]@row) = 1,

And then since we changed it to Multi Select, we can see if that cell HAS a selection (versus Contains):

=IF(COUNTM([Parent Names]@row) = 1, INDEX([Comment Version]:[Comment Version], MAX(COLLECT(Row:Row, [Parent Names]:[Parent Names], HAS(@cell, [Task Name]@row)))))

Keep in mind that your Grape row will still be blank, since its child does not have any data. Let me know if you'd like to see screen captures!

Cheers,

Genevieve

TYL

Hi Genevieve,

Thank you for your help and prompt response. It can work well now. Thank you very much!

Genevieve P.

Wonderful! I'm glad to hear it 🙂

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.