Index collect for latest project status

Kanchan M

Hi,

I have 2 sheets. Sheet 1 has a form where users can submit their project status each week. So sheet 1 has multiple lines with updates from each week for each project. In sheet2, for each of the project, I only want to get the latest status update. I tried using Index(collect({Project Status}, {Project Name from sheet 1},{Project Name}@row, {Update Date}, max{Update Date}), 1)

But there is only one row for which the project name will match the latest update date. So only 1 project gets the status update. All others show as blank.

Thanks for your help.

Darren Mullen

@Kanchan M I'd say the most straightforward way would be to use a checkbox column in your "Sheet 1" that determines if the update is the latest for the project based on the project name and date. Then you could use that checkbox in your Index/Collect from sheet 2 vs having to do all that calculation from sheet 2.

Make sense?