Index collect for latest project status
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.
Answers
-
@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?
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!