Looking for most recent date

Kimberly Loveless

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:

Genevieve P.

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

Genevieve P.

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

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.

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

Terence Garland

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.

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

Valerie_WPA

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

Genevieve P.

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

Valerie_WPA

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.

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