Return latest Update

Hi Guys

I have sheet - Incident Recoded, see pic below….

I have formula -

=IFERROR(INDEX(COLLECT(CHILDREN([Incident Description]@row); CHILDREN(Modified@row); MAX(CHILDREN(Modified@row))); MATCH("MTC"; CHILDREN([Primary Column]@row); 0)); "")

This formula works if on row 2 in the pic the Incidents is "MTC" but if the 2nd row is "PD" it returns a blank.

I want the formula to return the latest incident description for only incidents - "MTC"

Please advise

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

@Morena Please check your other thread. I believe we may have it answered there.

Jason Tarpinian

The MAX(CHILDREN(Modified@row)) is collecting EVERYTHING, not just the MTC tasks. So when the max (2025/02/17) doesn't match the collected range of MTC (2025/02/12), it returns your #INVALID VALUE argument. To fix it, you just need to COLLECT your MAX as well to include only the MTC tasks.

=INDEX(COLLECT(CHILDREN([Incident Description]@row), CHILDREN([Primary Column]@row), "MTC", CHILDREN([Incident Date]@row), MAX(COLLECT(CHILDREN([Incident Date]@row), CHILDREN([Primary Column]@row), "MTC"))), 1)

Just be aware if there are multiple max MTC dates, this formula would only return the first row. So if there is a chance to have multiple incidents on the same day, it might be best to either throw a JOIN somewhere in there to bring in all of them on the latest day, or use the Row# system column to truly bring in the latest.

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.