Help with MAX(COLLECT) Formula

Hello Community,

SmartSheet newbie and I have looked at several MAX(COLLECT) threads but am struggling to figure out what I'm doing wrong. In my workspace, I have two spreadsheets:

Audit History (contains entries with vendor name and start/end dates) - we audit on a routine frequency so names will repeat based on said frequency
Vendor List (contains vendor name, address, service categories)

What would be really helpful is to have a formula that pulls the most recent audit date of a vendor so I can figure out if they can remain on our vendor list. The formulas/errors I have tried are:

INVALID REFERENCE: =MAX(COLLECT({Audit History End Date}, {Audit History Range 1}, {Audit History Vendor Name}, {Audit History Range 2}), [Vendor Name]@row)

UNPARSeABLE: =MAX(COLLECT({Audit History End Date}, {Audit History Range 1}, {Audit History Vendor Name}, {Audit History Range 2}, ["Vendor Name"]@row))

Any help you can provide would be greatly appreciated.

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @mkchung

What does Audit History Range 1 refer to?

=MAX(COLLECT({Audit History End Date}, {Audit History Vendor Name}, [Vendor Name]@row))

Does this give you what you need?

Kelly

mkchung

Hi Kelly,

The Audit History Range 1 is the column I selected in the Audit History worksheet for the formula to look for the most recent audit date. When I entered the formula, I got this error: #INVALID REF

=MAX(COLLECT({Audit History End Date}, {Audit History Vendor Name}, [Vendor Name]@row))

Any thoughts on what I should try next?

Thank you!

Michelle Choate 2

Hi @mkchung -

Just as a note - best practice is to ALWAYS name your references. In a year, when you need to check a formula and add to it, you are not going to remember what 'Range 1' was. I always recommend using the sheet name then the column name.

That being said, I believe this article will explain a lot - https://community.smartsheet.com/discussion/73384/max-and-collect-to-find-the-latest-date-with-specific-criteria

Also the column needs to be a date column that you are returning the information into.

#INVALID REF refers to a sheet reference in curly brackets that doesn't exist. I would check to make sure your cross sheet references are accurate.

https://help.smartsheet.com/articles/2476176-formula-error-messages#invalidref

So maybe you clicked on a single cell instead of the entire column would be my first thing to trouble shoot. The other thing is that vendor that you are checking might not have a reference date. Maybe you need to try another vendor?

Also - I always suggest wrapping it in an IFERROR() formula. So it is

=IFERROR(MAX(COLLECT({Audit History End Date}, {Audit History Vendor Name}, [Vendor Name]@row)),"")

If you want it to say something instead of being blank if there is no reference, then you can put words in-between the "" at the end of the formula. For instance "NO DATE", etc.

Hope this helps!

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.