Creating a report by task owner and RAG status

Ollie Cater

Hello Smart Sheet-ers,

I am fairly new to SmartSheet so excuse me if this is breathtakingly simple. I need to create a report of the following in dashboard. I assume from using Sheet Summary.

It needs to show Task Owner, then how many tasks that are RAG status red, yellow, green, blue.

I'm using the below columns:

So for example I'd like to see the person's name on the Y axis, then 4 x bars (red, yellow, green, blue) next to them in the dashboard.

So I need to know (I think) what formula to set up in the sheet summary to enable me to get that data into a report....

Thanks in advance!

Ollie

Genevieve P.

Hi @Ollie Cater

If the goal is to get this data into a Dashboard, you'll actually want to create a separate blank Grid sheet to be your "Metrics" sheet and use formulas to create your calculations.

For example, you can use a COUNTIFS formula to count how many times a specific user appears with a specific status:

=COUNTIFS({Source Sheet Assigned To}, Contact@row, {Source Sheet Status}, Status@row)

See: Creating Cross-Sheet Formulas / COUNTIFS function

Then you can use this data as the source for your Chart widget, but you'd need to select each person's data as a separate chart:

Two different charts:

Is this what you were looking to do? I might suggest that a Pie Chart (see here) may look best in this case.

Let me know if this makes sense or if you need any additional help!

Cheers,

Genevieve

Bassam Khalil

Hi @Ollie Cater 

Hope you are fine, you can use the new report builder and use the grouping option first by task owner then by RGB and use the summary option to count for you.

Ollie Cater

Hi Bassam,

Thanks for the quick reply...do I need to have any Summary fields set up for this?

Ollie

Ollie Cater

Is there a way to make this more easily readable, say on a chart?

Genevieve P.

Hi @Ollie Cater

If the goal is to get this data into a Dashboard, you'll actually want to create a separate blank Grid sheet to be your "Metrics" sheet and use formulas to create your calculations.

For example, you can use a COUNTIFS formula to count how many times a specific user appears with a specific status:

=COUNTIFS({Source Sheet Assigned To}, Contact@row, {Source Sheet Status}, Status@row)

See: Creating Cross-Sheet Formulas / COUNTIFS function

Then you can use this data as the source for your Chart widget, but you'd need to select each person's data as a separate chart:

Two different charts:

Is this what you were looking to do? I might suggest that a Pie Chart (see here) may look best in this case.

Let me know if this makes sense or if you need any additional help!

Cheers,

Genevieve

Ollie Cater

Amazing. That's spot on. Thank you!

Genevieve P.

No problem! Glad I could help.

Ollie Cater

Hi Genevieve- can I just check one thing...

In the first formula above, where you have written "Source Sheet", presumably here I use the title of the sheet I would like to use as a source sheet?

Thanks in advance!

Genevieve P.

Hi @Ollie Cater

Do you mean this bit: {Source Sheet Assigned To}?

In this case, you would need to create a Cross Sheet Reference to the sheet you're looking to pull information from. If you simply adjust the text you'll get an error because the formula won't know where to go.

As you're typing the formula, click on this link that says "Reference Another Sheet"

It will bring up a pop-up window where you can search your Smartsheet account for the correct sheet, the select the column within that sheet to be the reference for your formula.

You'll need to do this twice... once for {Source Sheet Assigned To} and once for {Source Sheet Status}

This Help Article has more information and a brief video tutorial that you may find helpful: Cross-sheet formulas

Cheers!

Genevieve