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

Options
KevinHarrisCHT
KevinHarrisCHT ✭✭✭✭
edited 09/28/23 in Formulas and Functions

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

Tags:

Answers

  • John C Murray
    John C Murray ✭✭✭✭
    Options

    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
    KevinHarrisCHT ✭✭✭✭
    edited 09/29/23
    Options

    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
    John C Murray ✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!