Sheet Summary Report and Dashboard Widgets

SheaD

Hi!

Can I use a Sheet Summary Report to drive a Metric widget?

I have 25 project sheets that I would like to roll-up into a sheet summary report. I would then like to create dashboard metric widgets using this data. I am currently driving the metric widgets off of the individual sheets, but this is getting cumbersome as we add more projects...though not a bad problem to have!

Thank you!

Shea

Andrée Starå

Hi Shea,

Unfortunately, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

What I have set up for my team is this...

 

I take all of the data on each sheet that I want to gather into one place. I then add in an extra column that can later be hidden for each piece of data.

 

I then have a "Roll-up" sheet. my team can go to this sheet, select the next open row and link the cells from the other sheet. Since the cells in the project sheet are laid out across a row, they can all be linked at the same time. This saves them a lot of effort.

 

Because each sheet is set up the same way, each piece of data has its own column in the Roll-up sheet.

 

This Roll-up sheet is where I pull my metrics and reports from.

SheaD

Andrée - I thought as much. Thank you for the prompt response. I will add as an enhancement request. - Shea

SheaD

Paul - Thank you for the suggestion. The roll-up sheet sounds similar to a Sheet Summary Report, without it being a "report". I have a individual "Metric Sheets" driving off of each individual project sheet, and was looking for a way to report-out the metrics from within those individual metric sheets via a consolidated dashboard, without creating a roll-up that required manual linking to each metric within those individual metric sheets. However, it sounds like your project column / roll-up sheet solution may provide what I need with minimal manual linking (or at least less than what I was planning on doing). I'll see if that's feasible for us. Cheers!

Shea

Paul Newcome

Happy to help! 

 

The way I have it set up is that the columns for linking are to the far right of the sheet and highlighted a different color from anything else in the sheet.

 

I have that same number of columns on my Roll-up sheet (27).

 

I have instructed my team to go to the Roll-up, select the first cell in the first available row, then hold down Shift and press End. This highlights all 27 columns for that row on the Roll-up.

 

They then right click and select "Link cell from another sheet". Once they navigate to the appropriate sheet, they are instructed to scroll right until they find the first cell in the top row that is highlighted "unique color" (generally purple since I don't use that color anywhere else).

 

Click on that one cell.

 

Hold down Shift.

 

Press End to highlight that row all the way to the last column.

 

"Create Link"

.

Now the entire row is linked to those hidden columns containing the pertinent project data.

 

A few nice things to doing it this way...

 

Instructions are relatively simple.

Cell linking only has to be completed once for each project.

Multiple data points can be linked at the same time as opposed to having to do 27 individual cell links.

Projects are still managed from the individual sheets.

.

One additional thing I do is provide instruction on how to hyperlink the sheet into the appropriate row on the Roll-up.

 

This means I have a hyperlink to each sheet that can be built into reporting.

This allows me to pull a report based on whatever set of criteria and be able to jump straight to each individual sheet from the report.

 

This means that I can pull a report for John Doe. He can review the data for all of his projects in one place and can quickly jump to a specific project's sheet to make updates if needed.

 

Or I can pull a report for the execs that shows all projects that are behind schedule regardless of who is managing it, and the execs can see the important data at a glance and drill down easily if they wanted to.

.

Overall though I would say that the nicest part about it is being able to track all of my team's projects in one place.

 

It will take some time to set up depending on how many sheets you already have up and running, but once you get your backlog squared away, each new sheet will only take a minute or two to add to the Roll-up, and then it is done.

.

I have definitely gotten a lot of good feedback from the team since I updated the linking process to doing it this way. Previously they were doing 27 individual cell links for each project sheet. Now they only have one and done.

.

I would be happy to build a sample (and simplified) workspace out that I can share to you if you need ideas or help setting things up.

SheaD

Excellent - thank you for the detailed instructions. I have a good number of sheets, so I'll have some work to do over the next few days. Cheers!

Paul Newcome

Happy to help! 

Andrée Starå

Excellent!

Happy to help!

Andrée Starå

Paul's method is similar to the one I use as well.

I have an excellent template set that is using a similar structure.

Email me at andree@workbold.com, and I'd be happy to share it with you!

 

Sid Richardson

I would like to receive an update on this Enhancement Request, as I have same need. Thank you.

Roberto Conte

Agreed on receiving an update to this request. I would like the same functionality ! thank you.