Select MAX(Modified Date) where name = Jane Doe

Cara North

Hi,

I have a contact log form that populates a sheet each time a user creates an entry. Each user creates several entries a day. In a separate sheet, I want to report the date that each user created their most recent entry. How would I go about that?

Genevieve P.

Hi Cara,

You can use a MAX(COLLECT to gather this information. Try this:

=MAX(COLLECT({Modified Date Column}, {User Column in other Sheet}, [User Column]@row))

This assumes that the sheet you're calculating the data in has one column with contacts (I've called mine "User Column") and one column for the formula. The columns referenced {in these} are cross-sheet references to your contact log sheet. The one [in this] references the contact column in the sheet where you build the formula.

This formula would collect the MAX date from your Modified Date column in the other sheet, searching for the user in this current row. Does that make sense?

Here are some articles I used: MAX function / COLLECT function / @row function / Cross Sheet Formulas

Cheers!

Genevieve

Cara North

Thank you Genevieve!

Genevieve P.

Hi Cara,

You can use a MAX(COLLECT to gather this information. Try this:

=MAX(COLLECT({Modified Date Column}, {User Column in other Sheet}, [User Column]@row))

This assumes that the sheet you're calculating the data in has one column with contacts (I've called mine "User Column") and one column for the formula. The columns referenced {in these} are cross-sheet references to your contact log sheet. The one [in this] references the contact column in the sheet where you build the formula.

This formula would collect the MAX date from your Modified Date column in the other sheet, searching for the user in this current row. Does that make sense?

Here are some articles I used: MAX function / COLLECT function / @row function / Cross Sheet Formulas

Cheers!

Genevieve

Cara North

Thank you Genevieve!

Genevieve P.

No problem! Happy to help 😊