Creating a chart from a multi-select dropdown among multiple sheets?

KevinHarrisCHT

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

John C Murray

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

KevinHarrisCHT

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.

John C Murray

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