COLLECT Function | Smartsheet Learning Center
https://help.smartsheet.com/function/collectUsed within another function to collect specific values in a range that meet the provided criteria.
Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
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
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
- 67.7K Get Help
- 473 Global Discussions
- 200 Use Cases
- 514 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 520 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!