Rolling up counts from several sheets to a dashboard
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
- to show how many risks there in each PI Category
- 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,
Best Answer
-
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") + …
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers
-
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") + …
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Thanks @aravindGP for confirming my suspicions. I can still use the sheet summaries elsewhere so at least all that effort was not for naught!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives