PMO Template where I need a portfolio report of all current project statuses by most recent date

Megan Moyer Craig

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)

1. How can I extract to the project metadata first by latest date? My attempts have me getting all statuses and not just most recent.
2. 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?
3. What is the formula for extract by latest date?

Thanks so much, looking for any ideas :)

A

B

Bassam Khalil

Hi @Megan Moyer Craig

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)

Genevieve P.

Hi @Megan Moyer Craig

@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

Megan Moyer Craig

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))

Genevieve P.

Hi @Megan Moyer Craig

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))

Megan Moyer Craig

Still doesn't like it with this - ----

=INDEX({PROJ_1001_Status Updates Range 1}, MATCH([MaxDate]@row,{PROJ_1001_Status Updates Range 2}, 0))

Genevieve P.

Hi @Megan Moyer Craig

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))