Portfolio Summary Header Row

I have the Project Management Office Setup. The "Portfolio Summary Header Row" in the Project Intake Sheet got deleted and now all of the references are broken to each individual project.

Based on the formula in the Project metadata I'm guessing I need to re-create the "Portfolio Summary Header Row"?

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

I re-created the header row but it hasn't fixed any of the references for the individual project metadata. Is there a key piece I'm missing in this formula and how it works?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @project manager jeff

    If the top row with the column names was deleted, then yes, you'll need to re-create all of your references to that row in each INDEX(MATCH(MATCH formula, since the current reference no longer exists.

    To do so, click on that range:

    {Portfolio Summary Header Row}

    Then click Edit Reference

    Now select the newly created top row with the names in it. You'll need to do this once per-sheet in order to update the reference in each sheet.

    Cheers,

    Genevieve

  • So is it not some tag that I can do for my newly created row in the intake sheet? Because the reference still says "Portfolio Summary Header Row".

    I will have to go into each project metadata sheet to specifically click that top row? Because I have like over 50 projects so if I could avoid having to re-create that tasks, would be super helpful.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @project manager jeff

    The reference title will stay the same even if the content changes. Since the row was fully deleted out, all of the references broke as they pointed to that exact row and rowID (which now does not exist).

    If it was just a matter of the text being removed, then you could just add the text back in to the cells to have it populate correctly in your 50 sheets. However, unfortunately, since the error you're seeing is a REF error, this means the entire row is gone so all of your individual 50 sheets and their individual reference to that row will need to be updated to look at the newly created row.

  • So what would I make the formula to reference the newly created row?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @project manager jeff

    You'll just need to change where the reference is looking, but using Edit Reference then selecting the correct row.

    • Click on that range: {Portfolio Summary Header Row}
    • Then click Edit Reference
    • Find and select the newly created top row with the names in it.

    You'll need to do this once per-sheet in order to update the reference in each sheet.

    Cheers,

    Genevieve

  • hwgu
    hwgu ✭✭

    I also accidentally deleted the original header row and it broke all of my project references. When I add another row to the top of the sheet, SmartSheets is identifying it as a project instead of a header row and is assigning a new Project ID. How would you go about designating a new header row?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @hwgu

    You can change the Project ID column to be a regular text/number column. Then change the value in the Project Id cell for that row, and finally update the Project ID to be an auto-number column afterwards.

    When you adjust the column back, you'll need to specify that the "starting number" is the next number that hasn't been assigned:

    Cheers,

    Genevieve