MAX and COLLECT
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
Best Answer
-
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
Answers
-
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
-
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!!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!