Project Metadata Sheet no updating

KatieDi

Hello

I've completed the project intake form which has populated the Project Intake Sheet in the PMO Template set.

I've then followed the instructions and copied the Project ID into the Project ID column in the Project Metadata sheet. When I do this, the rest of the data, e.g. Project Name, Project Manager etc is just coming in as #NO MATCH.

I'm not sure how to get it to populate?

Any guidance gratefully received.

Thanks

Katie

Project Intake Sheet:

Project Metadata:

Sherry Fox

@KatieDi ,

Does your Project Metadata sheet use an INDEX/MATCH or a VLOOKUP formula to pull the data from the Project Intake Sheet? What is the formula you are using, and from what cell is that formula entered into.

KatieDi

Hi Sherry, it's using the ones from the template set which are:

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Name]$1, {Portfolio Summary Header Row}, 0))

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Category]$1, {Portfolio Summary Header Row}, 0))

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Manager]$1, {Portfolio Summary Header Row}, 0))

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Sponsor]$1, {Portfolio Summary Header Row}, 0))

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Target Start Date]$1, {Portfolio Summary Header Row}, 0))

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Target End Date]$1, {Portfolio Summary Header Row}, 0))

These are aligned with the columns in the Project Intake Sheet, e.g. Project Name, Project Category etc.

I followed the instructions for the template set to add to the intake form, copy the templates, then copy the Project ID into the Project Metadata sheet, but the calculation which is meant to just cascade through doesn't work.

My only thought is that it keeps referring to Portfolio Summary, but there isn't a sheet in the template set for that?

Katie

KatieDi

Is anyone able to help with this - I'm completely stuck. I've just added a new project today to the Project Intake Sheet and I can't get that one's metadata to update either.

David Tutwiler

There are two MATCH formulas here, so two things have to exactly match on the other sheet. If you are copying the project ID from one sheet to the other, then that shouldn't be the problem. The only thing left is the second MATCH, which appears to be looking to match the column name with the {Portfolio Summary Header Row}.

My suggestion would be to go into the formula editor and Edit the reference for {Portfolio Summary Header Row} and confirm that the names match exactly to your column headers and that the associated numbers match your column numbers. The INDEX formula is looking for a row number (found my matching your project id) and column number (found by matching your summary header). If either the column names don't match the summary header, or the column numbers in that lookup are off, then you won't find a match.

I hope that makes sense, and if you still need any help, can you post screenshots of the Edit Reference on the Portfolio Summary reference?

KatieDi

Hi David,

Thanks for responding.

I'm not great with these sorts of things, so I think I understand what you're saying! :)

My project metadata sheet looks like this when I click the formula in the Project Name line that currently shows #No MATCH.

The Project Intake sheet looks like this:

So I'm with you in the sense it can't match something which is why it's failing, but I'm not sure what it is that I need to change.

Katie

David Tutwiler

No problem, we'll walk through it together.

When you click on the formula that starts with =INDEX({Portfolio Summary Data}... can you click on the last reference in the list called {Portfolio Summary Header Row}? That should show a new option below the formula that says Edit Reference that is hyperlinked. If you click on Edit Reference, it should take you to another window that will pull up the sheet/column that is being referenced so you can check to see what it looks like.

A screenshot of that reference screen would be very helpful in solving this, I believe.

KatieDi

Brilliant!! Thank you. :)

Ok, I've clicked Edit Reference and I get this:

David Tutwiler

Is there a chance that isn't the right sheet to point it at? I notice that your SM-012 project is not on there.

What happens if you change that reference to your Intake sheet and select the row #1 that contains all of your column headers?

KatieDi

Fair point! It was still looking at the sheet in the template set.

I've changed it to the same row, but in the 'actual' project intake sheet and still no joy.

David Tutwiler

Gotcha. I think it would be worth checking all of your references just to make sure they are pointing at the correct sheet instead of the template. Can you check the first reference called {Portfolio Summary Data} and make sure it is pointed at your intake sheet?

David Tutwiler

Sorry, and the same goes with the reference {Project ID}. Make sure all 3 are pointed at your Intake sheet

KatieDi

That one looks like this:

David Tutwiler

How about the {Project ID} reference? It should be pointed at the Project ID row for that same sheet.

KatieDi

YES!!!!

That's working! I think I've got more issues with other things, but for now, I'll take that the metadata is updating at the very least!

Thank you, but I may be back if that's ok! 😊

Katie

David Tutwiler

Great news! I'm glad at least that part is working.

Always feel free to reach out to the community with any questions. That's what it is here for.