Formula that will pull the most up to date information from

Monica Lino

Hello

I have a sheet that gets new entries every month and each month, the status of the project is calculated and may update. I have another sheet that holds the project information and it needs to show the most up to date status of the project. I was wondering if that is possible to do by a formula that will take the most up to date status for that project and move it into another sheet. Something similar to Max Collect but instead of largest value, it calculates the latest entry based on date.

Thank you

Amit Wadhwani

Hi @Monica Lino

You can try creating a helper columns to get the recent status of the Project. In your Project Information sheet create one column called “Recent Date” and apply the below formula.

=MAX(COLLECT({date}, {project name}, [Project Name]@row))

This formula will get the information from sheet one, with these formula you will get the recent date.

Create another column to get the recent status and apply the below formula

=JOIN(COLLECT({Status}, {project name}, [Project Name]@row, {date}, [Recent Date]@row))

I hope this helps

 

Best Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

Monica Lino

Hi Amit,

Thank you so much for this. Is it possible to set up a formula without creating extra columns?