Index Collect + Max Date

Nick Horton

Hi there,

I'm having another head banging moment.

I have a sheet where we track all project comments. There are 3 different types of comments (Program, Project and financial)

I am trying to return a comment, where Comment Type = any of the above, and show me the Most Recent Comment of that type.

I came up with the formula below, which doesn't quite work.

=INDEX(COLLECT({Comment}, {Type}, "Project status report", {Date}, MAX({Date})), 1)

It works if the most recent date aligns with the status you are trying to find (i.e. project status is the most recent comment). I then want to get the most recent of the 3 program report comments, but as this isn't the highest date in the sheet it doesn't work. It then returns #invalid value.

Essentially I am trying to get the most recent comment of type X. Do I need to complete a nested Index(Collect) statement?

Thanks in advance

Paul Newcome

I would suggest a helper column on the sheet that you are trying to pull the most recent column to. This column will be a date type column and will have a MAX/COLLECT formula in it to pull the most recent date for that specific type.

Then you can use the INDEX/COLLECT to pull the most recent comment using a cell reference to this new date type column.

The reason for doing it this way is that you cannot nest COLLECT functions. So you need to first pull the most recent date separately before you can reference it in your INDEX formula.

Bassam Khalil

Hi @Nick Horton

Hope you are fine, it's better to do it in a different approach by adding a helper column check the status for each row, and record a specific symbol or status you can use in your formula or in a filtered report.

Nick Horton

Hi Bassam,

Thanks for your comment, but I'm not sure how this helps? Although I could be completely wrong.

My Comments sheet has 3 Columns in it; Type, Date, Comment.

On another sheet, in one column, I want to show the latest Comment of type Project Status.

In the next column, I want to show the latest comment of type Program status.

In the Image below, The Program Status (in Yellow) returns the commentary, as 20/02/21 is the highest date. If I use the formula above for Project Status (In Red) it doesn't work, as it isn't the highest date at all.

With your comment re a helper column, I'm not sure how this would help my current situation?

Any help greatly appreciated.

Cheers

Nick

Paul Newcome

I would suggest a helper column on the sheet that you are trying to pull the most recent column to. This column will be a date type column and will have a MAX/COLLECT formula in it to pull the most recent date for that specific type.

Then you can use the INDEX/COLLECT to pull the most recent comment using a cell reference to this new date type column.

The reason for doing it this way is that you cannot nest COLLECT functions. So you need to first pull the most recent date separately before you can reference it in your INDEX formula.