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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!