PMO Template Set Guidance

Good evening.... I'm a new Smartsheet user and attempting to setup my project environment using the Project Management Office Template Set. I have setup my Project Intake Sheet and have saved the Project level (copy for new projects) folder as a new project folder under the same workspace. According to the PMO template guide, I have to go to the new project's 'Project Metadata' sheet and paste the applicable 'Project ID' into the 'Project ID' field and the sheet should bring in the applicable data from my intake sheet. The sheet pulls in some of the data from the Project Intake Sheet, but some cells are looking for a 'Portfolio Summary Data' sheet, which I cannot find in the template set. Do I need to redirect the function back to the Project Intake Sheet where the data also exists? What I'm concerned about is modifying the linkage between the template set and not having full functionality between projects and the portfolio dashboard.

I receive a 'The linked sheet is not currently available - it may have been deleted or you may not have permission to open it' error when I click on the formula. It appears to be looking for 'Portfolio Summary Data', 'Project ID', and ' Portfolio Summary Header Row' sheet.

One would think that the template set would come with full functionality. I'm assuming that it works as intended and I just don't know what I'm doing yet. : )

Any guidance would be greatly appreciated.

Thank you!

Find more posts tagged with

Accepted answers

Andrée Starå

Hi @Scott Bales

The Portfolio Summary Data isn't a sheet. It's a cross-sheet reference from the Intake Sheet.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

All comments

Andrée Starå

Hi @Scott Bales

The Portfolio Summary Data isn't a sheet. It's a cross-sheet reference from the Intake Sheet.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Scott Bales

Andree,

Thank you for getting back to me.

The individual project's 'Project Metadata' sheet has a note that reads the following..

"This sheet acts as a landing place for key project information. Intake data comes from the Portfolio Summary sheet via lookup formula. TO ADD DATA Create a column for your new data. Be sure to select the appropriate column type.If this data is going to be cell linked to the Portfolio Summary, ensure that there is a matching column on the Portfolio Summary and that the order of the columns is identical on each sheet."

The function shows =INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Name]$1, {Portfolio Summary Header Row}, 0)) on the metadata sheet.

I'm assuming the 'Portfolio Summary sheet' the note references is referring to my main project intake sheet I have named 'Drivers Project Intake Sheet'. I created additional columns in the 'Project Metadata' sheet to ensure column names and placements align with my project intake sheet, but still no luck.

What is the point of the 'Project Metadata' sheet for each individual project? Can I not just link anything I need to pull in from the primary intake sheet straight into the applicable sheet and eliminate the need to update the metadata sheet every time I initiate a new project?

Let me know if you have any ideas, given the additional info. If not, I can share a few screenshots with you if you have time to do so.

Thanks again.

Roger R

what happened to Andree Stara response?. i am also new to smartsheets and having the same issue. prodesk support is a week out and not at all happy with the many community conversations with no information follow-up like this one. probably miss my dates

Roger Raymond

Roger R

Andree - "The Portfolio Summary Data isn't a sheet. It's a cross-sheet reference from the Intake Sheet."

How does this answer the question? where is it exactly and how do I modify it? I do not see anything in terms of a cross-sheet reference on the intake sheet.

Sandra Guzman

Hello @Roger R,

Sorry that you are not happy with some of the conversations from the community. When I started out, I personally found that this was the best place to do some digging to find solutions. I also found the responses to be very, very helpful. As a best practice if you are looking at an older post, it is sometimes better to just start a new discussion and include a link to the older post. The only reason I saw this was because I followed the initial post so your message happened to pop up via e-mail notification. In order for the message to be sent to the person you are engaging with - you need to include the @ and name (you will see a pick list) and you can choose from there.

It would be helpful to have more information to make sure that we are talking about the same thing... but if you are using the same template set, I think I can try to explain things a little further.

In Smartsheet there is an actual summary section that is available and can be used to pull data into a report. That is entirely different that the topic that was being explained in the comments above. So let's actually break down the comment that you referenced from @Andrée Starå .

"The Portfolio Summary Data isn't a sheet. It's a cross-sheet reference from the Intake Sheet."

Definition:

A cross-sheet formula references data in a different sheet to perform calculations or look up information in a specific cell.

Resource:

https://www.smartsheet.com/content-center/best-practices/tips-tricks/powerful-and-flexible-cross-sheet-formulas#:~:text=A%20cross%2Dsheet%20formula%20references,corresponding%20data%20from%20other%20sheets.

If you go to the Project Metadata sheet and click on the cell that has the formula, you will see the formula, plus the references that apply. You can click on the reference in question and it will show you the exact location where it is saved. You can also view the sheet name and column(s) that may have been selected.

Project Metadata Sheet

Reference: Portfolio Summary Data

Column(s) selected will always show highlighted blue borders. In this case, the summary data is highlighting all columns included in the Project Metadata Sheet.

Hope this helps!

Sandra

Andrée Starå

Hi @Roger R & thanks @Sandra Guzman for the mention,

I hope you're well and safe!

Sandra's explanation is spot on.

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Herbb5000

Hello @Sandra Guzman, I was having a similar issue and this explains it very well, however I have another question, it looks like the summary data is referencing a row, but what if I accidently deleted this row? I am not sure how to insert a new row to use for the summary data, I am also new to Smartsheets BTW

Sandra Guzman

Hi @Herbb5000,

Happy New Year! Glad to hear that my comment explained it well enough to understand.

The answer too your question will largely depend on what the intent is behind the summary data. There are different ways to capture data in the summary which start from basic copying of cell information only, to more complex formulas that sum up totals, etc.

If you can share the formula with me, I can help to explain what you would need to do.

All the best,

Sandra

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.