Looking for the most recent submission based off date

hello I am looking to build a formula that pulls the "Mid/ EOC Rating" from a reference sheet with the criteria that the ID's match, it is the most recent submission based on Evaluation Date, and that the Evaluation was an EOC not any other options

So far I have gotten to the below formula but it is still reading as invalid column value as it pulls up the current date today

=MAX({Tech Evaluations from Ships Range Date of Eval}, INDEX(COLLECT({Tech Evaluations from Ships Range Rate}, {Tech Evaluations from Ships Range ID number}, [Employee ID]@row), 1), " ")

Reference sheet: could have multiple submissions for each person

Best Answer

  • ChristianFinke
    ChristianFinke ✭✭✭✭✭
    Answer ✓

    Hi @Ali Simpson

    To pull the most recent "Mid/EOC Rating" for a specific ID based on matching ID, the latest Evaluation Date, and only for "EOC" evaluations, try this formula:
    =INDEX(COLLECT({Tech Evaluations from Ships Range Mid/EOC Rating},
    {Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
    {Tech Evaluations from Ships Range Type of Eval}, "EOC",
    {Tech Evaluations from Ships Range Date of Eval}, MAX(COLLECT({Tech Evaluations from Ships Range Date of Eval},
    {Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
    {Tech Evaluations from Ships Range Type of Eval}, "EOC"))), 1)

    Let me know if this works and if you have any further questions.

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

Answers

  • ChristianFinke
    ChristianFinke ✭✭✭✭✭
    Answer ✓

    Hi @Ali Simpson

    To pull the most recent "Mid/EOC Rating" for a specific ID based on matching ID, the latest Evaluation Date, and only for "EOC" evaluations, try this formula:
    =INDEX(COLLECT({Tech Evaluations from Ships Range Mid/EOC Rating},
    {Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
    {Tech Evaluations from Ships Range Type of Eval}, "EOC",
    {Tech Evaluations from Ships Range Date of Eval}, MAX(COLLECT({Tech Evaluations from Ships Range Date of Eval},
    {Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
    {Tech Evaluations from Ships Range Type of Eval}, "EOC"))), 1)

    Let me know if this works and if you have any further questions.

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

  • Ali Simpson
    Ali Simpson ✭✭✭

    You are a Legend! Thank you!

  • ChristianFinke
    ChristianFinke ✭✭✭✭✭

    @Ali Simpson

    Love to hear it! Let us know if you or your team need any Smartsheet help in the future!

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!