Collecting latest date from another sheet that has multiple matching cells.

I have a master sheet that contains all of the items we have on order. Each individual order typically has multiple items and a separate delivery date. I need a formula to look through the data to return the closest delivery date listed for that item. I have tried INDEX(COLLECT, which I use all the time, I've tried MAX(COLLECT and I keep getting error #INCORRECT ARGUMENT SET messages for both.

=MAX(COLLECT({BB Delivery Date}, {BB PARTS LISTED}, [Part List]@row))

=INDEX(COLLECT({BB Delivery Date}, {BB PARTS LISTED}, [Part List]@row), 1)

Here is a screen shot of the master sheet.

Is the problem here that there are multiple parts with the same name on the same row? Is there a better way or function to return the delivery date with out adding a helper date column next to each column and making the formula really long?

Any help would be greatly appreciated.

Find more posts tagged with

Accepted answers

All comments

Amit Wadhwani

Hi @ChrisUPC

You can try the below formula to get the latest date for the specific value

=MAX(COLLECT({BB Delivery Date}, {BB PARTS LISTED}, [Part List]@row))

I hope this solves your issue.

Best Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

Did my solution help you? Do not forget to hit the awesome icon.

ChrisUPC

I already tried that formula. I ended up writing a VERY long MAX(Collect statement that broke up the {BB PARTS LISTED} range into 25 individual ranges. I was just hoping for a simpler solution.

Paul Newcome

The issue is that you have one date column and multiple part columns. All ranges within a function must be of the same size/shape. So if {Range 1} is a single column then all of the other ranges within the same function can only be a single column.

You are going to have to write out a MAX/COLLECT for each part column and then wrap the whole thing in another MAX function.

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.