Lastest Update

Morena

Hi I have sheet - Incidents Recorded for the Month, which record the incidents that occuring within a month. see the pic below….

I want a formula that can return the latest Incident Description when a new incident is added. I have used the following formula -

=IFERROR(INDEX(COLLECT(CHILDREN([Incident Description]@row); CHILDREN([Record Date]@row); MAX(CHILDREN([Record Date]@row))); 1); "")

But this formula returns the 1st latest incident recorded.

Please advise

Melissa Yamada

Hello @Morena

Try this:

=INDEX(COLLECT([Incident Description]:[Incident Description], [Incident Date]:[Incident Date], MAX([Incident Date]:[Incident Date])), 1)

Paul Newcome

You wouldn't use the MATCH. You would use just another range/criteria set within the COLLECT function.

=IFERROR(INDEX(COLLECT(…………………………………………………………………………….; MAX(CHILDREN(Modified@row)); CHILDREN([Primary Column]@row), @cell = "MTC"); 1); "")

Melissa Yamada

Hello @Morena

Try this:

=INDEX(COLLECT([Incident Description]:[Incident Description], [Incident Date]:[Incident Date], MAX([Incident Date]:[Incident Date])), 1)

Morena

@Melissa Yamada

Thankx

I managed to make it work, now I need a formula that returns the latest incident description Added for ONLY Incident with "MTC"

Morena

Hi Guys

@Melissa Yamada @Paul Newcome

I used the following formula -

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

It works but the problem is that if the 1st row of the children is "MTC" then it's fine but if I change the 1st row of the children to "FAC" or something else then it returns a blank, some reason it picks up on the 1st of the children even if the 2nd row is something like "LTI" or "FAC", it will ignore and them if they are not on the 1st row of the children

Paul Newcome

You wouldn't use the MATCH. You would use just another range/criteria set within the COLLECT function.

=IFERROR(INDEX(COLLECT(…………………………………………………………………………….; MAX(CHILDREN(Modified@row)); CHILDREN([Primary Column]@row), @cell = "MTC"); 1); "")

Melissa Yamada

Hello @Morena

Since you'll be adding another set of criteria you'd want to use COLLECT function instead of MATCH function as what Paul mentioned above.

Morena

@Melissa Yamada @Paul Newcome Thanx! it working

Paul Newcome

Happy to help. 👍️