Rolling up counts from several sheets to a dashboard

Carroll Wall

I have 6 projects. Each has a risk register on a separate sheet

Each risk has one of 5 possible statuses each of which has a color, (Blue, red, green…).

Each risk also has a Probability/Impact score, grouped by 5s, 1-25

I have created 10 sheet summary fields on each sheet to count these.

I have created a sheet summary report including all 10 fields for all 6 sheets.

The intent is to create charts for a dashboard

1. to show how many risks there in each PI Category
2. to show how many risks there are for each color

However when I attempt to make chart on the dashboard, it includes all the zeros when what I want is just the totals

So for example. I would like it to show, 2 for the PI< 5 column, 5 for the PI 6-10 column, 2 for the PI 11-15 column, etc.

I fiddled with the data source and axis settings but I can't get it how I want it.

(BTW Yes I know the chart name is wrong)

Do I need to start over and just create summary fields on a separate metric sheet and pull the chart data from there?

Thanks for helping,

AravindGP

Hi @Carroll Wall

A separate metric sheet would be the way to go. You can write cross sheet countif(s) formulas to get the values from all 5 sheets by adding them to get a consolidated P1 <5 value, P1 6-10 value, etc. So something like =COUNTIF({Sheet 1 range}, "Blue") + COUNTIF({Sheet 2 range}, "Blue") + …

AravindGP

Hi @Carroll Wall

A separate metric sheet would be the way to go. You can write cross sheet countif(s) formulas to get the values from all 5 sheets by adding them to get a consolidated P1 <5 value, P1 6-10 value, etc. So something like =COUNTIF({Sheet 1 range}, "Blue") + COUNTIF({Sheet 2 range}, "Blue") + …

Carroll Wall

Thanks @aravindGP for confirming my suspicions. I can still use the sheet summaries elsewhere so at least all that effort was not for naught!