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

ChrisUPC

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.

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.