How can I create a chart based on two non-numerical fields?

yolan

Hi there!

This is probably very basic stuff, sorry for that.

I'm struggling to get some basic charts from a sheet that contains some fields related to project (Name, Project Mgr, Project Type, Health(red/amber/green)...)

This is my sheet

I'd like to have a chart showing how many red, amber or green each Project Mgs has.

Something like this, where each bar belongs to a different Project Mgr: Dave, Andy, Helen

Is this even possible?

When I create the chart I get the meessage: "The selected data cannot be reported"

Thank you!

Ryan Sides

Hi yolan, you'll need to build a second sheet that we typically call a "metric sheet". This has cross sheet references back to the first sheet. You would list the PMs in the first column, then create columns for Red, Yellow, Green. Where these two lists meet, you would create formulas that count instances of these combos on your first sheet.

Then you point your chart widget on your dashboard to your metric sheet.

Your formula for Dave and Red would look like: =countifs({PM Name}, "Dave", {Health}, "Red")

Where {PM Name} is your cross sheet reference pointing to your Program Mgr column and {Health} is your cross sheet reference pointing to your Health column.

If you need help with creating cross sheet references, you can see this video.

yolan

Thanks for your quick response!

I will watch the video and try it.

I was thinking on maybe having separate "helper" Red/Yelllow/Green columns with 0/1 values depending on the Health value, and then sumarize them in a report... Had not tried it yet, just an idea.

But the "metric sheet" will make things cleaner, especially as the Health field is not the only one I'd like to get on charts, and the amount of "helper" columns will grow quickly 😅

Thanks again.

yolan

Ok, so I love the idea, but I need to get one step back.

The data I want to chart is not really on a sheet but on a report, and I get them form the "sheet summary" values of ALL the project sheets I have on my workspace. That is why I built it as a report, so it will update automatically when project sheets are added/deleted form the Workspace.

So how can I create the "metric sheet" from that report to be able to chart the values?

Or if not possible, how can I create a summary sheet with all the summary data from my projects, which is updated automatically when new sheets are added/deleted?

Thanks again.

Paul Newcome

It sounds like the perfect use case for Control Center.

Aside from that, you can create a Summary Sheet, but you will need to manually create your cell links to each of the individual projects as they are created.

Ryan Sides

You beat me to it @Paul Newcome

yolan

Ok, thank you.

I guess the not-so-neat way of doing it would be to create some "helper" fields to separate the different values to be charted and then summarizing them in the report?

Ryan Sides

Yeah. That's your best bet other than Control Center

yolan

Thank your @Ryan Sides and @Paul Newcome !!