Project Intake and Project Metadata Sheet Link

Options

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.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    edited 02/22/22 Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    edited 02/22/22 Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • SteveE
    SteveE ✭✭✭
    Options

    Thank you very much!

  • Kate K
    Kate K ✭✭
    Options

    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.
    Options

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • dzx9ph
    dzx9ph ✭✭
    Options

    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.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • dzx9ph
    dzx9ph ✭✭
    Options

    @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
    Options

    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.
    Options

    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.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now