Help with MAX(COLLECT) Formula

mkchung

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.

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!