Return the latest date an event happened

CamNZ

Hello,

I would like some help to return the latest date an event happened that was hosted by a person.

In one smartsheet I have the data entry table, and another that I'm using as a summary table to reference to for convenience.

For example I'm trying to return.

e.g. 'when was the last date 'Joe Bloggs' hosted an event?' . I would want '28/03/2018' returned in the formula out of the list below:

Joe Bloggs 24/03/2018

Joe Bloggs 25/03/2018

John Jones 26/03/2018

Joe Bloggs 28/03/2018

Vlookup doesn't work for me (unless using it wrong) because it cannot return the latest date value.

Is there some way to find this with a formula?

Thanks.

p_bureau

Hi,

 

Since there is no array formula such as in Excel, nor a "MAXIFS" formula, you won't be able do to this unless you first sort your data (decreased order).

 

Paul.

Chris McKay

Hi CamNZ,

While Paul is correct that Smartsheet does not support array and there is no MAXIFS operator, there are definitely options available that don't rely on you filtering or sorting the data.

Try this:

=MAX(COLLECT(Date:Date, Name:Name, [Report Name]1))

Where the [Report Name] column contains the criteria you are matching against (i.e. the person's name).

Hope this is what you were after. Kia ora bro!

CamNZ

Hello Chris,

Thanks for that solution - that worked!

All sorted now.

Thanks.

 

 

Chris McKay

Hello CamNZ,

You are more than welcome. Glad I could assist.

Alfredo ARTEAGA

I tried something like this, but I am getting #INVALID COLUMN VALUE error.

Looks like the output is a date, so it cannot be in a text/number column. If I try to write it in a date column, it says is invalid value and adds a ' before.

Jeff Zumaris

If you write the formula in a column that is not a date you will receive that error.  To circumvent that add +"" on the end of your formula and you will get the result.  One thing to note when done this way you will also get a Time Stamp with it along with the date.