Collect Max date with multiple criteria referencing multiple sheets

Hello!

I need to pull in the most recent date with 2 reference criteria, a unique ID and matching text. There are 4 possible sheets that the date could be on. So, my formula would need to say, "If not on this sheet, then look at the next sheet" and so on.

Below is the formula I have tried, but it is giving me a #DATEEXPECTED error. My column and all 4 reference columns are date columns.

=IFERROR(IFERROR(IFERROR(IFERROR(MAX(COLLECT({HSTX Mobile - Forklift Bare Rental Inspect Range 4}, {HSTX Mobile - Forklift Bare Rental Inspect Range 2}, [Equip #]@row, {HSTX Mobile - Forklift Bare Rental Inspect Range 3}, "On-Rent"), 1), MAX(COLLECT({HSTX Mobile - TeleCrawler On/Off Rent Range 4}, {HSTX Mobile - TeleCrawler On/Off Rent Range 2}, [Equip #]@row, {HSTX Mobile - TeleCrawler On/Off Rent Range 3}, "On Rent"), 1)), MAX(COLLECT({HSTX Mobile - Crawler Crane On/Off Rent Range 4}, {HSTX Mobile - Crawler Crane On/Off Rent Range 2}, [Equip #]@row, {HSTX Mobile - Crawler Crane On/Off Rent Range 3}, "On Rent"), 1)), MAX(COLLECT({HSTX Mobile - Bare Rental Inspections for Range 4}, {HSTX Mobile - Bare Rental Inspections for Range 3}, [Equip #]@row, {HSTX Mobile - Bare Rental Inspections for Range 1}, "On-Rent"), 1)), "")

Any suggestions would be much appreciated!

Find more posts tagged with

Accepted answers

All comments

Carlee Schiffner

@Paul Newcome Hey Paul! Any thoughts on how to resolve this?

David Tutwiler

I'll let Paul comment on the methodology of the general search, but I think your formula is erroring because your last IFERROR essentially ends with "if you can't find anything then return a blank ("")". This isn't a date and if you have a strict date column then the formula will error when you put in a blank.

If that is the case, you could have the iferror put in some nonsense date like 01/01/2000 so you can do a report on all rows that didn't return a valid date.

Paul Newcome

The blank is fine, and I use it rather frequently.

The way your formula is currently written, you are basically comparing dates to blanks to numbers.

Looking at just one piece:

MAX(COLLECT({HSTX Mobile - Forklift Bare Rental Inspect Range 4}, {HSTX Mobile - Forklift Bare Rental Inspect Range 2}, [Equip #]@row, {HSTX Mobile - Forklift Bare Rental Inspect Range 3}, "On-Rent"), 1)

We see that the COLLECT can pull in a date or a blank (if there are no matching rows), and you are comparing that to the number one.

In each of your MAX functions, replace the 1 with a date that is way earlier than you know will ever be entered. I usually use something like

DATE(1900, 01, 01)

in these instances.

This way you have a date being output by each MAX to avoid errors there. Then wrap them all in a MAX and use an IF statement to say that if that final max is not equal to 1 Jan 1900, then output the final max.

=MAX(MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)))

=IF(MAX(MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01))) <> DATE(1900, 01, 01), MAX(MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01)), MAX(COLLECT(.......), DATE(1900, 01, 01))))

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.