Max / Collect / Contains Formula

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!!

Find more posts tagged with

Accepted answers

All comments

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. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.