Collect most recent date from another sheet based on certain criteria

Hello

I have a fleet maintenance log that has invoices per vehicle with a column that is a drop-down with multiple values based on the service provided.

I am trying to make a sheet that would be a quick reference to see the most recent service per vehicle per category.

Being multiple services will but under a category I planned on doing a formula with CONTAINS and listing all services I want to be under that category. Before I tried listing all of the services I tried the formula =MAX(COLLECT({date}, {erac unit}, [Unit Number]@row, {service ROW}, CONTAINS("Brake Pad - Front", {service ROW}))) Just to pull the most recent date for the service Brake Pad - Front but it is returning with 0 for all cells and not the date. If I could figure out the formula I would have the following service categorized as Breaks

Do I need to add a helper column on the Maintenance Log that has the category? But if I did that I will still have some that would need to have multiple categories as on one invoice I could have Breaks and Oil changes.

I am open to anything! I really just need something easy for my team to glance at and see exactly when an oil change or breaks (just an example as i will probably add more categories once I finally figure it out) was done last!

Thank you in advance!!!

Find more posts tagged with

Sheets

Formulas

Support

help

Accepted answers

Paul Newcome

Tyr an @cell reference in the CONTAINS function:

=MAX(COLLECT({date}, {erac unit}, [Unit Number]@row, {service ROW}, CONTAINS("Brake Pad - Front", @cell)))

All comments

Jordyn

@Paul Newcome, I have seen you have a handful of other complicated formulas. Do you have any guidance?

Paul Newcome

Tyr an @cell reference in the CONTAINS function:

=MAX(COLLECT({date}, {erac unit}, [Unit Number]@row, {service ROW}, CONTAINS("Brake Pad - Front", @cell)))

Jordyn

That worked!

How do I add to that formula to add the additional service? in the same formula, I would like it to search for all services to do with breaks.

Paul Newcome

You would be less specific in the CONTAINS function and just look for "Breaks" in the column on the right.

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.