Looking for most recent date

I have two sheets. The first is a tracker for up coming reoccurring tasks and the second is a copy of rows that have been completed.

In the first sheet I would like to collect the date from the "date" column in the second sheet if the "Combined" column values match that on the first sheet and if "Most Recent" is checked in the second sheet. I know that there has to be a way to do this but every formula I try to use gives one error or another when I try to add the most recent criteria. For reference I will include images of the two sheets below.

I know that it will probably involve an INDEX/MATCH (the second sheet could be reordered) but not sure beyond that. And I might just be blanking on something I know but if anyone can help that would be great!


This is the first sheet:

This is the second sheet:


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 06/25/21 Answer ✓

    Hi @Kimberly Loveless

    If I'm understanding you correctly you want to find the most recent or MAX date, based on criteria in another column. In this instance, instead of an INDEX(MATCH, we can use a MAX(COLLECT function to find the Max date!

    The COLLECT function will filter down the Date range to only look at dates that are associated with the identical "Combined" value. Then the MAX function will find the MAX date in that range.


    Try this:

    =MAX(COLLECT({Date Column Sheet 2}, {Combined Column Sheet 2}, Combined@row))


    Let me know if this works for you.

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 06/25/21 Answer ✓

    Hi @Kimberly Loveless

    If I'm understanding you correctly you want to find the most recent or MAX date, based on criteria in another column. In this instance, instead of an INDEX(MATCH, we can use a MAX(COLLECT function to find the Max date!

    The COLLECT function will filter down the Date range to only look at dates that are associated with the identical "Combined" value. Then the MAX function will find the MAX date in that range.


    Try this:

    =MAX(COLLECT({Date Column Sheet 2}, {Combined Column Sheet 2}, Combined@row))


    Let me know if this works for you.

    Cheers,

    Genevieve

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭

    That works. Thank you @Genevieve P

    Its kind of annoying that I cant just leverage the check box that I am already using in the second sheet to mark the most recent.

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 06/25/21

    Hi @Kimberly Loveless

    You could do that as well, no problem. There's lots of combinations of functions that can help you get to the same end-goal.

    If you want to go that route, you can use an INDEX(COLLECT instead of a MAX(COLLECT. Then one of your ranges and criteria will be the checkbox column and if it's checked!

    Like so:

    =INDEX(COLLECT({Date Column Sheet 2}, {Combined Column Sheet 2}, Combined@row, {Most Recent Checkbox Column}, 1), 1)

    However this adds an additional range into your formula, so the MAX(COLLECT may be simpler. It's up to you!

    Cheers,

    Genevieve

  • Hi @Genevieve P.

    Please could you help on a formula, I have an asset management database where we move assets, I have the main asset sheet and a asset movement sheet. I need to have the "location" of all assets updated on the main sheet by referencing the "Asset Tag" and latest "Date Recorded".

    Movement Sheet

    Main Sheet

    I have used the index max collect formula but i cannot get the reference to asset tag included in the formula, i get errors.

    Thanks

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Terence Garland

    Can you post the formula you tried and the error you're receiving?

    The structure would be something like:

    =INDEX(COLLECT({Location Column}, {Asset Tag Column}, @cell = [Asset Tag]@row, {Date Column}, [Date Recorded]@row), 1)

    I would also double check that your Asset Tag is stored in the same type of column and in the same way across both sheets (ex. Primary Column in both sheets or Text/Number column).

  • Is there a way to do this with a search in a row and not a column?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Valerie_WPA

    Can you post a screen capture of your sheet and clarify what you're looking to do (but please block out sensitive data)?

    I would suggest building a formula in the same sheet instead of across sheets if you're looking in that row. Then you can use the @row function within your ranges, something like:

    [Column 1]@row:[Final Column]@row

    Cheers,

    Genevieve

  • I would ideally like to be able to pull the last date inspected. But I do need to take into account "NA" if for whatever reason the property wasn't available.

    @Genevieve P.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Valerie_WPA

    MAX will automatically skip any text values since they won't be dates to evaluate. This means you can use my suggestion above to find the MAX date across your row by making the Range selection of cells from the first column to the last column with dates.

    E.g:

    =MAX(Jan@row:Dec@row)

    See: MAX Function and Create a Cell or Column Reference in a Formula

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!