PMO Template - Deleted header row

Hi guys!


I’ve been using the PMO template successfully for a while now, but this morning I accidentally deleted the Header info and now all of the metadata for the projects shows #REF for all of the fields.

I tried to recreate it as best as I could, but it’s still off. Attached are what they look like. Normally I’m the project metadata, I enter the project ID and the index match would fill in the blanks.

here is the existing formula that is tied to each metadata right now.


Project Name

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


Project Category

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


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


Target Start Date

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


Target End Date

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

Answers

  • Hi @andreww

    Once you've added back in a row at the top with the header information, you'll just need to go into your formulas and update the {reference} that looked at the previous header row, and now point it to the new header row.

    I'll bold the reference:

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


    Click "Edit Reference" then find the header row again and click on the row number to highlight the entire row. This should now update all of your other formulas in the same sheet to have the correct reference.

    Let us know if this worked for you!

    Cheers,

    Genevieve

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!