Lookup and Return newest Date


Hi there, looking for assistance

i am trying to create a formula

I want to reference a second Sheet called "Medical/Lost Time"

Id like it to look up the Shift entered in the Shift column and return the newest Date from the Injury Date column

I've created a Data Sheet with the Reference names for the shifts ([Primary Column]26

=MAX(COLLECT([Primary Column]26, {Medical/Lost Time Range 6}, "P1 Days"))

Best Answer


  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭

    For the newest Date from the Injury Column, you could use the MIN function, but I don't have much for you than that. I'm not too familiar with "Collect" functions.

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.


    Brett Wyrick | Connect with me on LinkedIn.


    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Scott Johnson

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    Or if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

    PMP Certified


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Scott Johnson

    Source Sheet Medical/Lost Time

    destination sheet Data

  • Scott Johnson
    Scott Johnson ✭✭
    Answer ✓

    found this thread and it worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!