PMO Template where I need a portfolio report of all current project statuses by most recent date
Hello! I would like to keep a continuous project status update for each project in an individual workspace, but only report the most recent one back at the portfolio level. I have a separate status update sheet in the project workspace (A). If I just link the column, it obviously pulls all the updates.(B)
- How can I extract to the project metadata first by latest date? My attempts have me getting all statuses and not just most recent.
- Should I not be trying to put it into the project metadata to then load into the portfolio metadata? Should it be a separate report first?
- What is the formula for extract by latest date?
Thanks so much, looking for any ideas :)
A
B
Answers
-
Hope you are fine, you need to use the Index-Collect formula using the max date as a criteria. if you like i can help you if you share me as an admin on sample workspace contain sample data (after removing or replacing any sensitive information)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@Bassam Khalil is correct! You could use an INDEX(COLLECT to pull this information across.
However it looks like you have a date column in your second sheet which should already return the MAX date from the other sheet, is that correct?
Ex:
=MAX({Date Range})
If you already have the MAX date in a date column on your second sheet, you can the use that cell as a criteria for your other formulas and just use an INDEX(MATCH instead.
Ex:
=INDEX({Column with data to return}, MATCH({Date Column}, [Max Date]@row, 0))
This will find the row that has the MAX date (that you have returned in your Max Date column) and return the data from the first column listed.
Let me know if you'd like screen captures or further examples of this.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
I can pull the date with your =MAX({Date Range}), but I am still having an issue with the status.
=INDEX({PROJ_1001_Status Updates Range 1}, MATCH({PROJ_1001_Status Updates Range 1}, [Max Date]@row, 0))
-
My apologies! I wrote the formula backwards... you'll want to have the MATCH in reverse, listing the value to match in the current sheet first.
Try this?
=INDEX({PROJ_1001_Status Updates Range 1}, MATCH([Max Date]@row, {PROJ_1001_Status Updates Range 1}, 0))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Still doesn't like it with this - ----
=INDEX({PROJ_1001_Status Updates Range 1}, MATCH([MaxDate]@row,{PROJ_1001_Status Updates Range 2}, 0))
-
What column is your Max Date being pulled into, in the sheet with your formula?
In my example, I called it [Max Date], but it looks like your column name for the Max Date might be [Project Update Date] is that correct?
If so, you'll want to reference this cell instead:
=INDEX({PROJ_1001_Status Updates Range 1}, MATCH([Project Update Date]@row,{PROJ_1001_Status Updates Range 2}, 0))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!