Dashboard Charts Not Updating Automatically

Justin Reys · 05/10/21

Hi,

Issue: Dashboard charts not updating automatically.

Source Sheet, Summary Sheet and Dashboard Setup:

Source Sheet: List of tasks, has (amongst other columns) [Frequency], [Status], [Due Date] and [Task Due Today] (Formula to determine if the [Due Date] is today's date).

Summary Sheet: Uses the Source Sheet as it's source. Has a list of dates that determine (1) Today's date and (2) the Monday to Friday dates relative to today's date. Countifs formulas to count tasks based on [Frequency], [Status] and [Due Date].

Dashboard: 4x charts that use the Summary Sheet as it's source.

When does the issue occur?

When I update a task [Status] in the Source Sheet (e.g. from empty to "Yes" (green tick)) and save the sheet.
Go to the dashboard and the errors are displayed / charts do not have any data.

How to 'get it to work':

Go to the Summary Sheet.
Wait for the formulas to update (I would see the values change)
Save and exit out of the sheet.
Go back to the dashboard. The charts will now start showing the data.

Would be good to get an idea of how to resolve this. Thanks!

Cheers,

Justin

Justin Reys · 05/12/21

Hi @Paul Newcome and @Heather D

Thanks but still unresolved (for the data updating).

@Heather D - thanks the copying the URL works. Marked your answer as 'no' to keep the thread status as 'unresolved'.

@Paul Newcome - the Source sheet will be updated throughout the day by someone going in and out of it. The Summary sheet (which uses the Source sheet and is used by the Dashboard) won't be something that users will go in and out of.

As a test, here's what I did in sequence.

Note: Today's date is 13/May/2021 and the Dashboard has a metric pointing to a field in the Summary sheet to show "today's date".

Go straight into the dashboard without going into the Source Sheet and Summary sheet. Result: "Today is 11/May/21".
Go into the Source sheet (update tasks and save), go into the Dashboard (but did not go into the Summary sheet). Result: "Today is 12/May/21". @Paul Newcome - if the linked cells automatically update, I would expect the result to be "Today is 13/May/21" with the Thursday bar having data.
Go into the Source sheet (and update tasks and save), go into the Summary sheet (where I can see the formulas refreshing and values changing) save the summary sheet and save then go to the Dashboard. Result "Today is 13/May/21" and the Thursday bar now has data.
Now the extra weird thing is that I went back to my Source sheet, updated more tasks then went back to the Dashboard (without going into the Summary sheet) and "Today" went back to "12/May/21".... 😫

While typing this reply, I did another test...

Taking into account what you guys said about TODAY() (that the sheet needs to be 'activated' for the formula to refresh), I replaced the TODAY() formula in the Summary sheet with a VLOOKUP to return "Today" from the Source sheet and the seems to work (so @Paul Newcome this is now working as intended).

TODAY() is weird.

Heather Duff · 05/10/21

@Justin Reys I wonder if it has to do with the fact that the formulas you're using as a source of your dashboard use the TODAY() function.

Check out the comments from @Paul Newcome on this recent post, as I suspect they may be helpful to you: https://community.smartsheet.com/discussion/comment/290621#Comment_290621

Hopefully that's the issue and Paul's recommendations will fix it!

Best,

Heather

Justin Reys · 05/10/21

Hi @Heather D

Thanks for the reply. Reviewed @Paul Newcome 's answer but I don't think it'll work for this case.

To add to my previous post:

Both the Source and Summary sheets use the TODAY() function.
My use case is to update tasks during the day and on a daily basis (daily operations team task list) and the dashboard was intended to view the changes in real time. This means the workaround of creating an automated task to update a hidden date column will only work for the start of day but not as the tasks are being completed throughout the day.

I did consider using a form in the dashboard to poke the source sheet (e.g. form to update a dummy field in the source/summary sheets to refresh the data) but forms don't seem to be available (I thought it was??).

The other potential workaround is to have summary formulas back into the source sheet (which isn't ideal and I probably have to create further workarounds to stop them from being archived).

Any other options or thoughts?

Cheers

Paul Newcome · 05/12/21

The solution for the TODAY function would only be needed at the start of the day anyway. Throughout the day "as tasks are being completed" means that someone has to activate that sheet in some way to be able to indicate the task is completed. Marking something completed will update the sheet that is being accessed as well as any other sheet that contains cell links and/or cross sheet references in real time. It is just that initial "first activation" of the sheet that you need to worry about.

Heather Duff · 05/12/21

@Paul Newcome good thinking!

@Justin Reys FYI - Forms can be placed on dashboards using the Web Content widget. Just copy the URL to your Smartsheet form and use that URL for the web content widget.

Justin Reys · 05/12/21