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

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

PaulPerger

This is how I thought it would work... but it wasn't.

However, your confirmation that I wasn't crazy caused me to dig deeper and after a lot of "messing around" with it I figured out what was going on. My Task ID was changing. (It is a hidden column and I wasn't seeing it on both sheets so I didn't notice that it was different...) Once I fixed that, everything started working properly.

THANK YOU KELLY!!!!