Creating a graph in Dashboard

1996mustang

Please see the attached snip-it. I am counting the checked boxes in the columns and I want to display this in a graph format in a Dashboard.

SS Count.PNG

When I add a graph widget to the dashboard using the checkbox count totals, I get a message that says the data cannot be displayed in a graph format? I tried linking the total to another sheet and using that sheet for the graph but that didn't work either.

Any Suggestions?

Paul Newcome

@1996mustang You would type out

=VALUE(

Then in the formula helper box there should be a blue link that says something along "Reference another sheet". Click on that and a new window should pop up. Select the appropriate source sheet from the list on the left, click on the cell you want to link, then click on the blue box in the bottom right corner that says something like "Insert Link".

The window should disappear, and you should now see something along the lines of

=VALUE({Source Sheet Name Range 1})

Mary Royston

Hey @1996mustang

I believe it is because they are checkbox columns that you are getting that error.

You may need to provide more details on exactly how you linked the cells to another sheet, because that should have worked. But basically what you can do to get your chart is to create a new sheet called something like "Phase Metrics" or whatever, and then make that the sheet that counts everything using cell linking, or a formula. For the count, you can use cell linking or a simple formula like

=countif({reference the phase column from your original sheet}, 1)

See Screenshots: how to lay out your "metrics" sheet.

Reference the data in your dashboard, and the chart will pop up:

Hope that helps, let me know if you need more help!

1996mustang

I created a new sheet where the checkbox count is linked. However, I keep getting the same message.

Graph Infor.PNG

Phase Count.PNG

I must be doing something wrong.

Genevieve P.

Hi @1996mustang

How are you selecting the data for the chart? Your set-up looks good in the "Phase Count" image, however to get that into a graph you'll need to highlight all the cells that you want to appear in the graph.

For example, from "Phase 1", down to the corner of the value 0 across from "Phase XIII", selecting the entire data. Does that make sense?

Paul Newcome

@1996mustang You cannot do a straight cell link for this because the source data is being stored as a text value. You would need to use the VALUE function with a cross sheet reference instead to convert them to numerical values which can then be used in your chart widget.

Genevieve P.

@Paul Newcome to the rescue! I completely missed that the numbers were appearing on the left of the cell (noting that Smartsheet is reading them as a text value) instead of the right of the cell (as a number).

Great catch!

Paul Newcome

@Genevieve P. Between that and the source data being in a checkbox type column (only way to get "numbers" in those is to have them output as text values to begin with).

@1996mustang Really though my suggestion would be to remove the calculations from the source sheet entirely and use @Mary Royston's solution of a COUNTIFS on the second sheet. If you NEED them on the source sheet, then you can use my first suggestion of the VALUE function on the second sheet.

1996mustang

how do I use the Value function with a cross sheet?

thanks

ds

Paul Newcome

@1996mustang You would type out

=VALUE(

Then in the formula helper box there should be a blue link that says something along "Reference another sheet". Click on that and a new window should pop up. Select the appropriate source sheet from the list on the left, click on the cell you want to link, then click on the blue box in the bottom right corner that says something like "Insert Link".

The window should disappear, and you should now see something along the lines of

=VALUE({Source Sheet Name Range 1})

1996mustang

😁Paul,

Thank you for the feedback and yes that worked.

Best Regards,

ds

Paul Newcome

Happy to help. 👍️