"The selected columns cannot be charted" - pie chart widget problem

NTDSC ✭✭✭✭
edited 08/08/22 in Smartsheet Basics


I am trying to make a simple pie chart on a Dashboard, and I'm running into a bizarre issue with the columns I'm trying to select.

The source for the chart is a sheet summary report (see below screenshot). It's just one row. I've highlighted the 4 different groups of metrics that I'm looking at: project status (green), funder (blue), disease focus (orange), and region (pink).

The problem is that I can successfully make a pie chart for the columns highlighted in green and blue (see two screenshots below), but not for the ones highlighted in orange and pink. And the one that I really need right now is the orange one (disease focus).

As soon as I try to select the columns for disease focus or for the regions, the error message pops up with "The selected columns cannot be charted. Please select a different set of columns."

If I try to combine disease focus with status for example, the error doesn't appear, but only the status figures appear in the chart. If I select "all columns," only the figures for status and funder appear.

I also tried to go into the sheet summary (the source of the report) and change the formulas that the counts for disease and region are based on, to make them match the structure that Status and Funder are in, but that didn't help either.

I can find a workaround for this urgent need by just using Excel or something, since it's a super simple chart, but I would really appreciate some Smartsheet expert help in fixing the issue! We will ideally need a live-updating dashboard.

Thank you in advance!!


Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    That is because both of those groups are coming through as text values. How are those values being entered into the source sheet?

    NTDSC ✭✭✭✭

    @Paul Newcome Aaahh. When I exported the report to Excel to make a chart that way, a little flag popped up on those cells saying they were text values, and I wasn't sure why!

    The disease focus columns are in checkbox format, so in order to have the top row show a number (with the running total #) I had to add +"" to the end of the formula. So then for the sheet summary, I just input a formula that pulled each of those cells in the top row (Example: =[Lymphatic Filariasis (LF)]1, =Onchocerciasis1, etc)

    I just changed the sheet summary formulas to a different format, e.g. =COUNTIF(CHILDREN(Onchocerciasis1), 1), and that worked!! I definitely thought I had already done that, but....I guess it was still a text format somehow.

    It looks like I had done the same thing for the regions columns too, although I didn't need to, since those aren't checkboxes. I took out the +"" in the formulas and those are charting great :)

    Thank you so much!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad you were able to get it sorted!