MAX and COLLECT

PaulPerger

A different phrasing of my earlier question with a better "picture" to explain what I need.

I need to look up the MAX date for a task # on a sheet which houses all of the historical data for the completion of various tasks.

So, Two sheets. One Sheet is a "Master Task Sheet" with each task only appearing once. I need to add a formula to this sheet to look up the most recent completion date on sheet two. Sheet two is a history sheet with one line for each time the task has been completed.

I thought I could use the MAX and the COLLECT functions to "Collect" the data based on task ID and return the MAX from the "Completed Date" column. But I am apparently not understanding the syntax.

Any help would be much appreciated

Kelly Moore

Hey @PaulPerger

You are correct, a MAX/COLLECT will give you what you need. Collects have the syntax COLLECT(range where the data is you need, range1, criteria 1, range 2, criteria 2, etc). Since you will be collecting a date, the column you are placing the formula into must be a date formatted column.

=MAX(COLLECT({Completed Task List Completed Date column}, {Completed Task List Task ID column}, [Task #]@row)

Since this has cross sheet references, you must physically build these references - you cannot simply copy paste this formula

Does this work for you?

Kelly

https://help.smartsheet.com/function/collect