Project Intake and Project Metadata Sheet Link

SteveE · 02/17/22

New user here.

I downloaded the PMO Office Template Set and added a "Business Area" column to the Project Intake Sheet. I want to link that column to the Project Metadata Sheet. My goal is that for each project, when I copy/paste the Project ID from the Intake Sheet to the specific Project Metadata Sheet the Business Area field is populated along with the other fields from the Intake Sheet.

Genevieve P. · 02/22/22

Hi @SRI PM

If you've added a new column to the Project Intake sheet, the first step is to make sure you repeat the Column Name in the very top row of this sheet.

This is because that top row is being used as a reference in a cross-sheet formula in your Metadata Sheet.

Screen Shot 2022-02-22 at 11.59.31 AM.png

Then in your Metadata sheet, you'll need to do the same thing. Add the column, then add the Column Name to the top cell (I've highlighted it in blue).

In the second row, drag over the formula from the cell next to it:

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

This formula looks at the entire Intake Sheet, finds the Project ID that matches and then finds the column name that matches in those top rows.

Screen Shot 2022-02-22 at 12.06.52 PM.png

You can see that my cell has the formula in it because of the blue arrow on the right. Note that you'll want to make this new column update to the Folder that you'll copy for every project, so that after you've done this once it will be copied for all your other projects.

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Genevieve P. · 02/21/24

Yes, exactly. There currently isn't a way to reference a column name in a formula, so the helper row repeats the column names in order to index what column you want to find data within.

Genevieve P. · 02/22/22

Hi @SRI PM

If you've added a new column to the Project Intake sheet, the first step is to make sure you repeat the Column Name in the very top row of this sheet.

This is because that top row is being used as a reference in a cross-sheet formula in your Metadata Sheet.

Then in your Metadata sheet, you'll need to do the same thing. Add the column, then add the Column Name to the top cell (I've highlighted it in blue).

In the second row, drag over the formula from the cell next to it:

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

This formula looks at the entire Intake Sheet, finds the Project ID that matches and then finds the column name that matches in those top rows.

You can see that my cell has the formula in it because of the blue arrow on the right. Note that you'll want to make this new column update to the Folder that you'll copy for every project, so that after you've done this once it will be copied for all your other projects.

Let me know if this makes sense and works for you!

Cheers,

Genevieve

SteveE · 03/08/22

Thank you very much!

Kate K · 09/20/22

Hi there. Follow Up question...

I accidentally deleted the top row from the template.

I replaced the top row in the intake sheet with the same name as the column names however, my Project ID column has defaulted to a project number and I cannot change it. My metadata links are no longer working. Do you know how to fix this?

Genevieve P. · 09/21/22

Hi @Kate K

I believe I answered you on another post - you've done the first step! (Adding in the top row again). Now you'll just need to adjust the formula {range} to look at the correct, new row:

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

dzx9ph · 01/31/23

I'm having a similar issue when attempting to use a workspace from the PMO office template set that didn't use the portfolio folder (which may not be relevant).

When I attempt to update the project metadata in the individual project folder, I get a message of "#unparseable" or "#no match). If I select "Edit References" I see

{Portfolio Summary Data}

{Project ID}

{Portfolio Summary Header Row}

My question is: "Where does the Portfolio Summary Data" come from? Is that the intake sheet?

I know my column names must be identical and that the order of columns must be identical, must I also have exactly the same columns in my project metadata sheet as the project intake sheet?

Genevieve P. · 02/01/23

Hi @dzx9ph

Yes! The {Portfolio Summary Data} is the Top-Level Project Intake Sheet. The range is the entire sheet and all its columns. This is why the column names repeated in the top rows and the Project IDs must be the same - it's using those two values to match across the sheets.

The {Project ID} range allows the formula to find a matching project ID number to select the correct row from that Project Intake Sheet.
Then the {Portfolio Summary Header Row} finds the matching column name (referencing the top row cell in the current sheet and searching through the first row in the Project Intake Sheet).

This then enables the formula to find the right cell to bring back from that row.

You don't necessarily need the same number of columns, you just need to ensure there are two matching values for the formula to find across sheets: the Project ID down the Project ID column, and the column name across the top row.

If you're still getting an error, it would be helpful to see a screen capture of your project metadata sheet with the formula open.

Cheers,

Genevieve

dzx9ph · 02/14/23

@Genevieve P. - Thanks for your help. I finally figured out my error when I understood better how to manage references. I am leaving notes on what had caused me headaches, in case anyone else runs into issues with the project management template (my issue was that I had inherited a half-used workspace that only used parts of the template, so the references weren't working).

The elusive {Portfolio Summary Data}, {Project ID}, {Portfolio Summary Header Row} are indeed managed in "Manage References". They should reference the Project Intake Sheet.

{Portfolio Summary Data}, - you should select the entire sheet as the reference.

{Project ID}, - You should select the entire Project ID column as Reference

{Portfolio Summary Header Row} - You should be sure that you have a header row in text format that exactly matches the column names--I was missing the header row (I had to insert a row at the top, change my column properties to text/number so I could create the header texts for the referenced header row and then change my column properties back on my intake sheet).

You can create your own references in manage references, but be sure in the "Sheet reference name" field at the top that you name them exactly as they are spelled in your formula.

jlong7542 · 02/20/24

https://community.smartsheet.com/discussion/comment/320363#Comment_320363

Genevieve, if I'm understanding correctly, I need to have the row below the header row that's identical in order to use the metadata sheet properly?

Genevieve P. · 02/21/24

Yes, exactly. There currently isn't a way to reference a column name in a formula, so the helper row repeats the column names in order to index what column you want to find data within.

lav_bratis · 10/11/24

Hey,

I had the exact same issue. I have accidentally deleted the first raw. I fixed the problem by correcting the references, specifically the

{Portfolio Summary Data}, - you should select the entire sheet as the reference.

This has helped me a lot.

I have one question though, do I need the second Portfolio Summary reference here?

Thanks

Genevieve P. · 10/15/24

Hi @lav_bratis

Yes! Those will be two different references, although this window is not showing the full name. See that all 3 are "in use".

It's likely that one is the whole sheet reference: {Portfolio Summary Data}

But the other is just the header row: {Portfolio Summary Header Row}

Which is needed later on in the same formula. 🙂

lav_bratis · 10/16/24

Dear @Genevieve P.

you are right. I was later exploring myself the reference option in order to figure it out. I got it all now thanks to Your support guys.

Cheers

Project Intake and Project Metadata Sheet Link

Best Answers

Answers

Categories