Return Value based on Date Column, Primary Column MAX(Collect
Hey everyone, I need help I have a sheet that has students assessment submission. Students take the same assessment multiple times. I am wanting to pull the Final Grade of the most recent submission by Student. I have tried MAX(Collect and keep getting 0 I have also tried Index(Match and have been unsuccessful. I created a an example of the columns. In this example I would expect the value for kjdfue Most recent grade submission to be "Pass" based on their submission on 2/10/22. Can anyone help?
Answers
-
Hi @Leah Brown
There are a few ways we could do this; the easiest may be to add a helper column in your source sheet which identifies the Max Date, so that you can then use this in your formula.
For example, you could put this in a Checkbox Column:
=IF([Assessment Submit Date]@row = MAX(COLLECT([Assessment Submit Date]:[Assessment Submit Date], [Student ID]:[Student ID], [Student ID]@row)), 1, 0)
Then once you have this checkbox, you can use it as a criteria to filter for in your INDEX(COLLECT formula, something like this:
=INDEX(COLLECT({Final Grade Column}, {Student ID Column}, [Student ID]@row, {Max Date Checkbox Column}, 1), 1)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!