# Looking for most recent date

Options
✭✭✭✭✭✭

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:

Tags:

• Employee
edited 06/25/21 Answer ✓
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• Employee
edited 06/25/21 Answer ✓
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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.

• Employee
edited 06/25/21
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• Options

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

• Employee
Options

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).

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• Options

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

• Employee
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• Options

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.

• Employee
Options

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)

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!