How do I collect the most recent row data under a parent?

I have a sheet that I collect our vehicle information on each month.

I manually move new rows to under the correct parent row.

The issue I'm having is I can't find a formula to collect the most recent data from the children rows.

I want the parent row in each circled column to behave the same way latest comment does and the information I am trying to collect isn't numbers or dates.

Any ideas on a formula that could work?

Find more posts tagged with

Formulas

Accepted answers

All comments

Lucas Rayala

If you need to update the parent row with the most recently updated cell in a column, you'll need to create a helper column and have an automation record the date any cell in that column changes. Then you write a formula that pulls the data from the most recently updated cell for the children of that parent row, based off the helper column with the recorded change date.

Ridley_HK

@Lucas Rayala any ideas on how to write the formula? I've got no idea as it is a bit beyond my understanding

Lucas Rayala

Try the below formula.

=IF(COUNT(CHILDREN(Month@row))=0,"",INDEX(COLLECT([Target Row]:[Target Row], CHILDREN([AutoDateColumn]@row, MAX(@cell),1))

Note:

"[Target Col]:[Target Col]" is the column you want to summarize (replace "Target Col" with your column" name)
"[AutoDateColumn]@row" is the name of the column you created to with the automation (replace "AutoDateColumn" with your column name).

Ridley_HK

@Lucas Rayala I can't seem to get your formula to work.

I change the formula to:

=IF(COUNT(CHILDREN(Month@row))=0,"",INDEX(COLLECT([Email]:[Email], CHILDREN([InspectionDate]@row, MAX(@cell),1))

My month column is a drop down not a date field, would that make a difference?

Basically is all I want is the email parent row to = the most recent child row just like a newest comment box, but I can't figure it out for the life of me!!!

Lucas Rayala

@Ridley_HK, If you want the email that's associated with the most recent Inspection Date for the children under the header, then you have basically the correct formula. I may have mislead you because I was missing a couple of parentheses -- one was missing for "children" and "max".

=IF(COUNT(CHILDREN(Month@row))=0,"", INDEX(COLLECT([Email]:[Email], CHILDREN([InspectionDate]@row), MAX(@cell)),1))

Does this work?

Ridley_HK

Hi @Lucas Rayala,

The formula just comes up as unparseable.

=IF(COUNT(CHILDREN(Month@row))=0,"", INDEX(COLLECT([Email]:[Email], CHILDREN([InspectionDate]@row), MAX(@cell)),1))

Lucas Rayala

Sorry, let's get back to basics:

When you say that you want your columns to work the same way that "Latest Comment" works, what exactly do you mean? Latest comment provides the latest comment by row, and doesn't add anything to the parent row.

I assumed you were trying to gather, at the parent row level, the data from the most-recently updated child row.

To get the most recently updated data from a row, you need to have a date to reference. You have a "Date of Inspection" column -- you can use that as a reference, IF you want the data corresponding to the most recent "Date of Inspection". If that's not the right date, then you need to get a date.

Your formula mentions [InspectionDate]@row -- that's either a new column that's not in your original screenshot, or you are incorrectly referencing your Date of Inspection column.

If you want the most recently updated row, you can use the column type "Modified Date" -- this provides the date/time a row was last modified. We can build a formula referencing that. If you need to get specific as to when an individual cell within a row is updated, then you need a series of helper columns and automations. Initially I thought that was what you were looking for, but I am not sure now.

So, the question is, what do you want to use as a reference date? Once you clearly define that, then you can create a formula.

Ridley_HK

Hi @Lucas Rayala

You are right, this is what I want - "assumed you were trying to gather, at the parent row level, the data from the most-recently updated child row."

Date column I need to use is date of inspection - a modified column won't work for my purpose if I change something in a previous row, it would then bade the most recent info of that row rather than the last line added.

I am wanting this formula to equal the most recent data in each column - that will be the confusion.

It's so annoying because my number or date columns are so easy I've just used =MAX(CHILDREN)).

=IF(COUNT(CHILDREN(Month@row)) = 0, "", INDEX(COLLECT(Email:Email, CHILDREN([Date of Inspection]@row), MAX(@cell)), 1))

The above formula is coming up as incorrect argument

Sign in to join the conversation!

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.