Find Latest Date for With a criteria in rows

I have a form that my field team inputs the action they make in customer, the same form is used for every customer, In Another sheet, I have the visit controle, and in this other sheet, I want to know the last visit my team made to each customer.

So I need to use a MAX formula for each specific customer.

The only solution I found was to user forms to enter the response in the upper rows, so my Vlookup brigs the latest entered visit, but it is not failure proof, so I would like the formula to look for the latest instead.

Thanks,

Find more posts tagged with

Formulas

Accepted answers

Devin Lee

Hey @Carloscmatos,

Try this formula:

=MAX(COLLECT({Ref 1:Date:Date}, {Ref 2:Customer:Customer}, [Customer]@row))

All comments

Devin Lee

Hey @Carloscmatos,

Try this formula:

=MAX(COLLECT({Ref 1:Date:Date}, {Ref 2:Customer:Customer}, [Customer]@row))

Jeff Reisman

Hi @Carloscmatos

I wrote this up back in 2020 when I faced a similar challenge. I show my flawed solution and then explain how the correct solution I found works. Mine was for finding earliest date, but I could easily be adapted to find latest date. Perhaps it will help you.

https://community.smartsheet.com/discussion/69081/find-the-row-with-the-earliest-created-entered-date-that-matches-certain-criteria#latest

Carloscmatos

https://community.smartsheet.com/discussion/comment/314630#Comment_314630

Thanks for both of you who answered the question, but @Devin Lee was simple and got the job done perfectly!

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.