Chart Live Update

Hello,

We are experiencing an issue with the chart functions within Smartsheet. We are currently using Smartsheet to collect and process help desk tickets. We have a form hosted on our intranet where users can submit help desk tickets. Those are collected and processed by our help desk team all in one sheet within Smartsheet (Screenshot 1). We then have a second sheet that provides some simple, summary statistics of those help desk tickets by month by status (Screenshot 2). Finally, we are experimenting with a 'dashboard' of sorts that just provides a visual summary of the information compiled in the second sheet (Screenshot 3). This final piece is where the problem lies. We have it set to update every 2 minutes, and it appears to be functioning properly in terms of rendering the data as expected. However, when anything changes on the original source sheet (Screenshot 1), all of the charts fail (Screenshot 4). The only way to fix the issue is to navigate to the second, summary sheet (Screenshot 2) to, in-effect, synchronize the middle-man. Any way to fix this that doesn't involve pointing the charts directly at the form data?

Screenshot 1.png

Screenshot 2.png

Screenshot 3.png

Screenshot 4.png

Find more posts tagged with

Dashboards

IT & Operations

Cell linking

Comments

Sasan

Jacob,

Thanks for bringing this to our attention and thanks for including screenshots. It looks like the issue is beyond Charts as the metric widgets are showing invalid messages. Would you be able to provide a screenshot of (screen2) with the underlying formula in let's say [Submitted]3 cell?

Thanks

Sasan

Jacob Laan

No problem, see below (Screenshot 5).

Not sure if it's relevant or not, but something else I'm noticing is that if there are changes to the source sheet (Screenshot 1 above), when I navigate from the dashboard to the summary sheet, all the calculated cells show an error for a split second (Screenshot 6), as if the cells just haven't loaded yet, and then the errors go away and the data appears. So, it's almost like if there hasn't been some interaction with the summary sheet since a change to the source sheet has occurred, Smartsheet knows that the source changed, but it hasn't processed that change in the summary sheet, and the dashboard, which is once removed can't resolve the discrepancy until the summary sheet is refreshed....something like that.

Screenshot 5.png

Screenshot 6.png

Sasan

Jacob,

Given past the split second and your summary sheet has #s, and a forced Dashboard refresh by you, do the metric and charts show data and populate?

Thanks

Sasan

Jacob Laan

Sasan,

Yes, if I update the summary sheet first, and then the dashboard second, the dashboard charts render as expected, but not all users who have access to the dashboard would necessarily have access to the summary or source sheets, and we were hoping that the dashboard would reflect a real-time (or relatively recent) view of the underlying data source without the need to manually refresh the underlying data source every time a change occurs.

Sasan

Jacob,

Not sure this is about manually having to refresh the underlying sheet. It's more about a coincidental timing. Your summary sheet updates automatically when new data appears in source sheet. Dashboard widgets when refreshed, pull data from your summary sheet. In this instance, again timing, when the widgets where looking up the data in your summary sheet, the summary sheet was getting updated with source sheet data and in that split second it had not calculated the numbers. Therefore, dashboard widgets did not have #s to display. I think the mid-air collision of summary sheet getting updated and dashboard looking up the summary sheet is the issue. I hope that helps.

Thanks

Sasan

Jacob Laan

Sasan,

I'd be inclined to agree with you except that I can recreate the issue every time there is a change to the original data source, and each time, the only way to fix it is to navigate to the summary sheet and, in-effect, 'refresh' it. In addition, once a change has occurred at the underlying data source, and the charts fail to load in the dashboard, refreshing the dashboard itself doesn't resolve the issue either. That is to say, if it were a random issue, caused just by the incidental collision of the change and the refresh call, I would expect it to resolve itself upon a subsequent call.

Sasan

Jacob,

Ok, good to know. We need to troubleshoot this and get back to you. It's isolated to the summary sheet and underlying formula. Would you be able to share a screenshot of the cross-sheet formula cell selector screen?

Thanks

Sasan

Jacob Laan

Sure thing.

Wasn't sure which exactly you wanted so here is:

Cell reference for Month Total Column - Summary Sheet (Screenshot 7)
Cell reference for Month Status Column - Summary Sheet (Screenshot 8)
Cell reference for Status Metric - Dashboard (Screenshot 9)

Screenshot 7.png

Screenshot 8.png

Screenshot 9.png

Sasan

Jacob,

Thanks. This is helpful. We are finding that the @cell reference might be the culprit and a bug in cross-sheet referencing. In the mean time, here is a work around:

1. Add a column to your source sheet called "Month" (You can choose a different name).

2. Use a MONTH formula to extract the month # from "Date Requested" column to the "Month" column.

3. In the summary sheet change your COUNTIF formula to reference the "Month" column, without using the @cell.

That should help the situation in the interim, till we fix the @cell bug within a cross-sheet formula.

This should fix the entire flow from source-->summary-->dashboard and update all automatically when new data is added in source.

Let us know if this interim solution works for you.

Thanks

Sasan

Jacob Laan

Sasan,

Yep, that did the trick. Thanks for stepping through this with me, I really appreciate the great customer service!

Sasan

Pleasure! And thank you for raising this so that we can fix for all other customers that would use @cell in cross-sheet formulas.

Sasan

Bar243

Do you have a date when the @cell issue will be resolved?

I am experiencing the same issue as above.

Thank you

Sign in to join the conversation!

Quick Links

Trending discussions

Mr
hi would like to create a simple patient tracking system for our small clinic where i can record the time & duration a patient appeared in our establishment. Using a central sheet where it records all the data to be accessible by the admin. There will also be Four (4) forms to be accessible by the three department. Concept…
Forms
On the App - "In Smartsheet forms, is there a way to capture or upload an image directly into a specific column cell (per row) without using the Attachments field? I need each section of the form to have its own dedicated image, not just general attachments for the row."
Smartsheet Mobilizer Kit2/Best Practices
Hi - I am launching Smartsheet sessions at my organization for the next 3 months. The SS Mobilizer Kit2 Workspace I saved a few years back, the files are not loading. Is there a new resource kit? Also, I am writing up some best practices for our users: Examples: Names, separate first and last name into two columns. Email…