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

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

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

Trevor Hobbs

Thank you everyone for your comments here! They helped me to fix this issue when I deleted the header row of my intake sheet and lost the references to the Metadata Sheet.

Genevieve's instructions are spot on! But if you're new to using Smartsheet, you may not know know how to follow them.

Here's what I did:

Once you've added back your new "Row 1" on your intake sheet, with matching titles in each cell that match the Column Heading on your intake sheet, you need to go back to your Metadata sheet and double click in each of the cells of Row 2 in your Metadata sheet. When you do this the formula becomes editable, and you can simple click on each portion of the formula. When you do this, an additional pop up menu will appear with a clickable link to "Edit Reference". When you click that link, yet another pop up menu will appear, displaying your Intake Sheet.

Since you deleted your header row on the Intake Sheet, you deleted the row that the Metadata Sheet was trying to reference for the "Portfolio Summary Header Row" portion of the formula. After selecting the "Edit Reference" link for each cell in your Metadata Sheet, you'll want to select your new Row 1 again to add back the reference.

You'll need to do this for each Cell/column on your intake sheet, based on the columns you need to reference. That should fix things for you and get the Metadata sheet to populate the information from your Intake Sheet again :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.