Summarizing from multiple sheets (count, drilling down, etc.)

Kim H

I've been searching the Community section but am having a hard time finding the exact answer I'm looking for, so I hope someone can help.  I haven't worked with Reports or Dashboards before, so I'm just not sure exactly how to set up my sheets for this new project.

My company is conducting reviews of our office locations a few times a year, so I've set up 13 sheets, one for each office.  The main (not primary) column is a drop-down of 16 Category choices for the issue that the office has to work on - Scheduling, Job Margin, Realization, etc, along with start/end dates, assigned to, status and comments. 

What I need help with is summarizing the information on a dashboard or report or something.  Ideally, I'd like a page where it shows the total number of issues assigned to each category, so Job Margin - 2, DSO - 10, etc., maybe in a chart but it doesn't have to be.  Then you'd click on one of them (like Job Margin - 2) and you would see the detail behind the 2 issues - it'd show the sheet it's on (which has the office name) plus all the other columns.

That way, the team that is keeping track of the issues would be able to have one place where they can view all the job margin issues across all 13 offices, and then drill down to see the detail without having to open all the other sheets.  Is this possible?  If so, if someone could please help point me in the right direction, I can figure out how to do each step by reviewing the documentation on this site.

Thanks in advance for your help!

Andrée Starå

Hi Kim,

There are a few ways to set this up. Here's one.

1. Collect all the metrics on the same sheet in the sheet's top (or somewhere else) and then cell-link to a Master Metric sheet or use cross-sheet formulas to collect the data on a Master Metric Sheet and from that data you can then create reports and dashboards.
2. In the dashboard(s) you'd show what's relevant and when they click to get more information, it would open a report that shows the details of that category.

Summary.

Dashboard with KPI (collected on the sheet or a metric sheet) > KPI opens individual reports as needed.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I was thinking something along the lines of having a report for each category along with a "Metrics" sheet that pulls together all of your counts.

 

You could then use a series of Metrics widgets on a dashboard to build out a table of all of your counts and set the widget interactions to open the appropriate reports.

Andrée Starå

Paul,

That's basically what I described 

Paul Newcome

Haha. Sorry about that. I am not sure why, but I was having trouble following yours the first few reads. You point out the similarities makes it a little more clear. This has been a very long week. Ugh.

Andrée Starå

No worries!

It was your turn!

Yes, it's been a busy week, and it's - Squirrel!  not over.

Kim H

Thank you, Andrée and Paul! 

So I'd have 16 reports and one metrics/summary sheet that would total the # of issues by category using cross-sheet formulas.  Then when you say I could use a series of metrics widgets on the dashboard, do you mean I'd have 16 metrics widgets showing the total # of issues for each category, and then set it so that if they click on it, it opens the report for that category?  I hope I'm understanding that correctly.  So this would be instead of actually having 16 report widgets on the dashboard, correct?

Thanks again for your help!

Andrée Starå

Happy to help!

Yes, that's correct!

Best,

Andrée

Andrée Starå

Another way to set it up would be to total everything on each sheet and then use cell-linking to collect it on the Master Metrics Sheet and then total all sheets and calculate any other values you want.

Both alternatives work and it's a matter of preference and how much work it involves.

Best,

Andrée

Paul Newcome

HAHAHA! 

Kim H

Thanks, Andrée!  I was thinking about doing a summary on each sheet, too - I'll have to see how it looks, because I'd need 16 summary cells for the categories and I just didn't want the sheets looking too "busy"   I'll test both ways out - thank you for your help!

Paul Newcome

Correct. 16 report widgets on a dashboard can get very busy very quickly and becomes a data overload to anyone who is looking for it.

 

Andree's suggestion (that I just reworded HAHA) would provide the overall totals at a glance with the ability to drill down on the details in a single click while the Sheet Name column in a report allows the user to drill down even further.

 

Andree's other suggestion of having the total counts on each sheet would also be useful as it keeps that overall data readily available and can give you an overview of each office on an individual level.

Andrée Starå

You're welcome!

What I do most of the time for my clients is that use hierarchy (parent/children) so they can easily hide the metrics but it's there if they need it.

Best,

Andrée

 

Paul Newcome

You could set the summary rows as children of a row that simply says "Expand to view summary" or something to that effect. The summary row(s) could then be collapsed to keep the sheet looking a little cleaner.

Paul Newcome

There I go again... Same thought, different words. This time though I was already typing when you posted, so I didn't see your response until I had posted mine. Hahahahaha

Andrée Starå

Haha! Squirrel!