Return latest Update

Morena

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

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.