Max / Collect / Contains Formula

Sarah.Cordell

Good afternoon!

I am trying to collect the most recent date from a source sheet for a given activity type that contains a specific word (on the same source sheet). I feel like I am almost there, but it is giving me the most recent date for all activities, not just the one I am looking for with contains. Thoughts?

Here is what I used:

=MAX(COLLECT({Go-Live Date}, {Member Name}, [Member Name]@row), CONTAINS("health check", {Activity Type}))

I hope this makes sense - thank you in advance!!

DKazatsky2

Hi @Sarah.Cordell,

What kind of field (column type) is Activity Type?

Sarah.Cordell

It is just a text column.

Melissa Yamada

Hello @Sarah.Cordell

try this:

=MAX(COLLECT({Go-Live Date}, {Member Name}, [Member Name]@row, {Activity Type},"health check"))

Sarah.Cordell

Thank you for your assistance, @Melissa Yamada and @DKazatsky2 !

Melissa - when I try that formula, it comes back with empty results.

=MAX(COLLECT({Go-Live Date}, {Member Name}, [Member Name]@row, {Activity type}, "health check"))

Paul Newcome

Try putting the CONTAINS inside of the COLLECT function like so (to include the @cell piece as is):

=MAX(COLLECT({Go-Live Date}, {Member Name}, [Member Name]@row, {Activity Type}, CONTAINS("health check", @cell)))

Sarah.Cordell

hI @Paul Newcome ,

When I attempt that formula, it gives me an incorrect argument error. Perhaps because the activity type is on the other sheet I am pulling from, so it seems like it is not allowing the @ cell the same way I can do when referencing something within the same sheet.

Paul Newcome

I use it like this all the time. Double check that each of your cross sheet references are referencing only a single column each.

Sarah.Cordell

@Paul Newcome - I tried it again and it worked! THANK YOU!!

Paul Newcome

Happy to help. 👍️