Creating a chart from a multi-select dropdown among multiple sheets?
Hi there,
I am running into a roadblock on creating a report and graph for the below scenario.
We have a bunch of event sheets (about 10) that all have an 'assigned to' column that is a multi-select contact list and it varies between sometimes being assigned to just one person and sometimes to multiple. I am trying to create a graph that would show outstanding tasks (where complete is unchecked) for each of the contacts. In attempting to spin up a metric sheet, the COUUNTIF formula seems to only be available for a single sheet of data, not across multiple. Is there a clean way to do this? Or do I need to make a metric sheet with every sheet having its own countif formula and then adding those sums together?
If that is the case, any ideas on how to make a report where a manager can go in, see a report of each contact and what outstanding tasks they have spanning all 10 sheets?
TYIA
Thanks,
Kevin
Answers
-
You can certainly COUNTIF across multiple sheets, but it might test your sanity a bit. You hinted at it in your post:
=COUNTIF({Sheet1_UserList},[Username]@row)+COUNTIF({Sheet2_UserList},[Username]@row)+COUNTIF({Sheet3_UserList},[Username]@row)+..... and so on
-
Thanks @John C Murray !
My sanity may definitely get tested there. I may hold off on moving forward since it would only show values but not allow a manager to drill down into those details. Potentially going to try to move forward with making that a single select contact list for ease of reporting.
-
Hi Kevin.
It is quite common to have a sheet that contains a single row of counts, sums, averages, percentages, etc.
The data in each cell can be displayed in a Metric widget on a dashboard. The widgets can then be configured to open more detailed information in the form of another dashboard, a report, a dynamic view, or even an entire sheet.
Good luck with your design. I hope it works out OK
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!