Return Value Based Off Of MAX Date, With Specific Criteria

Options

Hey guys, I'm sure this is easy/right under my nose, but any help would be appreciated.

I have a master sheet that receives multiple evaluations for four different employees from their supervisors. Each of the four employees will have hundreds of evaluations sent to this sheet, with the date, their name, and their score in respective columns.

In a Sheet Summary field, how can I retrieve the most recent evaluation score for a specific employee, based off of the most recent date? Each employee is searchable, i.e "John Smith", because this sheet will only house evaluations for the same four employees.

Any help would be tremendously appreciated!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @MichaelNewkirk

    You can do this by using a combination of the INDEX( COLLECT and MAX functions!

    Try something like this:

    =INDEX(COLLECT([Score Column]:[Score Column], [Name Column]:[Name Column], "John Smith", [Date Column]:[Date Column], MAX(COLLECT([Date Column]:[Date Column], [Name Column]:[Name Column], "John Smith"))), 1)


    Here's how this works:

    =INDEX(

    COLLECT(Column to bring back,

    First Column with Names, "Name",

    Date Column, Max Date for that Name)))

    , First matching row)


    Let me know if that makes sense and works for you.

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @MichaelNewkirk

    You can do this by using a combination of the INDEX( COLLECT and MAX functions!

    Try something like this:

    =INDEX(COLLECT([Score Column]:[Score Column], [Name Column]:[Name Column], "John Smith", [Date Column]:[Date Column], MAX(COLLECT([Date Column]:[Date Column], [Name Column]:[Name Column], "John Smith"))), 1)


    Here's how this works:

    =INDEX(

    COLLECT(Column to bring back,

    First Column with Names, "Name",

    Date Column, Max Date for that Name)))

    , First matching row)


    Let me know if that makes sense and works for you.

    Cheers,

    Genevieve

  • MichaelNewkirk
    Options

    Genevieve, thank you! That absolutely did the trick, and the breakdown of how it worked helped a lot as well. Thank you so much!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Wonderful! I'm glad I could help 🙂

  • CARO885
    CARO885 ✭✭
    Options

    Hello, I have a similar issue where I need to return the most recent entry based on Action Type :monthly inventory and Name.

    So I need to know what was the latest "Monthly Inventory" entry for each technician, I need to return the monitors, tool kits, etc

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @CARO885

    Were you able to adapt the formula above for your sheet? Here's what it would look like with your column names:

    =INDEX(COLLECT(Monitors:Monitors, [Technician Name]:[Technician Name], "John Smith", Date:Date, MAX(COLLECT(Date:Date, [Technician Name]:[Technician Name], "John Smith"))), 1)

    If you're looking across sheets:

    =INDEX(COLLECT({Monitors Column Reference}, {Technician Name Column}, "John Smith", {Date Reference}, MAX(COLLECT({Date Reference}, {Technician Name Column}, "John Smith"))), 1)

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!