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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!