Creating a report by task owner and RAG status
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
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi Bassam,
Thanks for the quick reply...do I need to have any Summary fields set up for this?
Ollie
-
Is there a way to make this more easily readable, say on a chart?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Amazing. That's spot on. Thank you!
-
No problem! Glad I could help.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives