Template Auto Linking for New Projects

Sarah123

I'm trying to have a sheet that is in my template be added to an overall sheet via cell linking. Is there a way to get a group of cells to link to a high level sheet when a new project is created via the template (using control center)?

What I'm doing is making a sheet showing all the resources and teams on a project. I currently have it working for the individual project but I would like to have an overall sheet that will be able to see the resources and teams for all the projects. Right now, it's a manual process that i have to update whenever a new project is created to add cell linking to this main sheet.

Paul Newcome

If you have access to Control Center you can set it up to provision new projects through there and have the links automatically created on a Summary Sheet. Otherwise you're unfortunately stuck with manual.

However...

You can "bulk cell link" if you structure correctly. If you highlight a group of cells that is 4 columns wide and 2 rows tall, you can right click and go through the same way you would cell link and then select a group of cells 4 columns wide and 2 rows tall from the source sheet and have all of the cells linked in one step.

I personally have leveraged this 2 different ways.

I will go ahead and select a group of cells in the target sheet to match the source and create my links. Then you can click and drag the border of the cell on your target sheet to move the linked cell to the appropriate location.

or

If I am linking vertical data in the source sheet to horizontal data in the target sheet, I will create some hidden helper columns to the far right of the source sheet and use cell references to pull the data from the vertical layout to the horizontal layout right there in the source sheet (in the same order as what I have in my target sheet). Then I can cell link across that top row in bulk.

Paul Newcome

I am fairly certain you would need to create a new datamesh each time a new project is created because the datamesh is a separate "app".

Paul Newcome

Have you set it up in Control Center to be the Summary sheet?

Sarah123

I haven't. I have another sheet as the summary sheet but would it be possible to setup these other sheets as summary sheets to add into multiple columns of information? I've attached a picture of the columns from my individual projects that I would want linked in (it would be all the rows in case there are additional resources added on the projects).

Paul Newcome

When setting up a Blueprint Summary, there should be an option at the bottom to add a new summary sheet. I personally haven't done any testing with this, but that may be what you need to set up.

Sarah123

I tried this but it doesn't seem to be working. I don't see any updates in the sheet.

Paul Newcome

I'll try to do some testing when I get a moment to see if I can get a second summary sheet working and let you know.

@Genevieve P. @Andrée Starå Do either of you have any experience with a second summary sheet?

Sarah123

Thank you! @Paul Newcome

Genevieve P.

Hi @Sarah123

What about setting up a Dynamic Report instead? This would automatically include specific rows from new sheets provisioned within that project. Here's documentation on this type of report: About Dynamic Report Scope

Cheers,

Genevieve

Sarah123

I tried to do it with a report but the data doesn't show up properly in the charts (it's not separating out properly).

I'm trying to get the roles to show up with the hours and project names. I can get it to work if I do it via sheets but when I try with reports it won't show up properly.

This is what it looks like when I use a sheet instead of a report:

Paul Newcome

Try grouping your report by role and then including a summary field in the report to sum the hours.

Sarah123

I just tried that and it shows me the hours per role but it doesn't show the portfolio. I need to show the number of hours per role per portfolio. Somehow I keep missing that 3rd piece unless it's entered in as a sheet and not a report. Not sure if I'm missing something...

Genevieve P.

Hi @Sarah123

Paul's suggestion is exactly what I was going to say. In your images it looks like you may just need to toggle the "Switch rows & columns" option.

From:

To:

Cheers,

Genevieve

Paul Newcome

Are you looking to show a stacked column that contains Onboarding in one color and Sarah's Test as another color for Admin Ops? Then have another stacked column next to it with the same colors for the same portfolios but for "Different Role"?

Sarah123

Yes! I want to show the different colors for something (whether it be the role or the portfolio). but i want to see all portfolios and all roles with the hours being tracked. I'm trying to make up another example with a different role to see if that's all I needed to do. :-)

Paul Newcome

Yes. Definitely get some more sample data in there so you have more to work with.

Sarah123

I put some more projects and portfolios in the data and for some reason it's only allowing me to select 2 pieces of data (I need to select 3 - portfolio, role, and hours). This was the issue I was having with reports before and why I moved to using sheets to gather the data because I could have all three pieces of information.

I have the sheets and columns selected and the groups I have are Roles and then Portfolio with the summary for the Hours.