Summary Sheet/Report/Cards/?

Jeff Kline

I have no idea how to even approach this. I have played with using a report summarize some data using different views, but the formatting so limiting that I cannot get anything useful.

I have a main sheet that tracks evaluations. For this summary I want to show just a small bit of data. I have columns that I want to use in the Summary: Date Due, Examiner, Month Due, and Month Due Name. What I would like is a super compact summary for each month that shows how

many evaluations are due for each examiner.

In Access I was able to create small rectangles - with the month name at top, and each examiner name on the left and a number on the right representing the number of evaluations. Using small type sizes I was able to get all 12 months in a single window.

I have played with creating summaries in a report. Using various views and various groupings, and various sorts. But there are usually 160+ examinations to track and 6-8 examiners.

I tried collapsing The data, but then I lose the name of the examiner.

What I want is to be able to take a quick glance at the data, hopefully with minimal scrolling, and see what each examiner has assigned to them in total and what is assigned by month. I would like to then display this (in a dashsboard) Next to a grid view of the same data, so I can then assign cases that are unassigned.

Here is what the monthly summary showed in access. Left Column is the name of Examiners:

Any help would be appreciated.

Genevieve P.

Hi @Jeff Kline

I would personally use a separate Sheet for this (perhaps called "Metric Sheet") and then use cross-sheet formulas to gather the numbers needed.

For example, you could have the first column list out the Examiner names, and each column after that be associated with a Month. Then you could use a cross-sheet COUNTIFS formula to find your count per-month, like so:

=COUNTIFS({Examiner Column}, Examiner@row, {Month Due Name Column}, "March")

See: Create cross sheet references to work with data in another sheet

This assumes your "Month Due Name" column has the names spelled out. If you need help defining the month criteria, it would be helpful to see a screen capture of how your source sheet is set up in Smartsheet, but please block out sensitive data.

Then you can add this to your Dashboard - either as Metric Widgets or as a published version in a Web Content widget, or even in a Report. How you display it is up to you!

Cheers,

Genevieve

Genevieve P.

Hi @Jeff Kline

I would personally use a separate Sheet for this (perhaps called "Metric Sheet") and then use cross-sheet formulas to gather the numbers needed.

For example, you could have the first column list out the Examiner names, and each column after that be associated with a Month. Then you could use a cross-sheet COUNTIFS formula to find your count per-month, like so:

=COUNTIFS({Examiner Column}, Examiner@row, {Month Due Name Column}, "March")

See: Create cross sheet references to work with data in another sheet

This assumes your "Month Due Name" column has the names spelled out. If you need help defining the month criteria, it would be helpful to see a screen capture of how your source sheet is set up in Smartsheet, but please block out sensitive data.

Then you can add this to your Dashboard - either as Metric Widgets or as a published version in a Web Content widget, or even in a Report. How you display it is up to you!

Cheers,

Genevieve

Jeff Kline

Thanks Genevieve, That looks workable. I will play with this over the next day or so and see if I can get it to work.

Thanks!

Jeff

Jeff Kline

Genevieve, That worked like a charm. Thanks so much for your help. I was able to create a dashboard, with individual Metric Widgets for each examiner and the monthly summaries.

Jeff

Genevieve P.

I'm glad to hear this worked for you! 🙂