Linked Cells not updating in report

Mathijs Muller

I have created a dashboard to see the daily job status. One of the functions is to see how many jobs are open on Today's task list. This is based on countifs on the date of the task list.

The formula's are sometimes working, sometimes not. When we make an update on the tasklist, the report does not show the latest updated data, but the figures go back to the nrs from the day before.

Is there a delay in updating? The tricker seems to be that when a change is made, instead of showing the updated data, is shows old data. Anyone having similar issues, and more important, any idea how to solve this?

Genevieve P.

Hi @Mathijs Muller

Can you copy/paste exactly what formula you're using? It sounds like you'll have the TODAY function somewhere in your formula.

Keep in mind that the TODAY function needs an update or Save to happen to the sheet that houses the formula in order for it to recognize what the Date is Today. See this article where it states the following:

"Smartsheet will update all instances of the TODAY function in a sheet to the current date when any of the following actions are taken:

• You open the sheet and save it
• You add a new row to the sheet from a form submission
• You make changes to the sheet from an update or approval request
• You update the sheet by way of a report
• A linked cell updates the sheet
• You open the sheet being referenced in a cross-sheet formula "

If your formulas are on a separate sheet than your data, one of the previous actions will need to happen on this separate sheet.

I would suggest putting your formulas in to a Sheet Summary Field (see here) to keep it on the sheet where the new rows are created.

Then you can select Sheet Summary Fields as a source for Metrics in a Dashboard:

Let me know if this makes sense and has resolved the issue you were seeing!

Cheers,

Genevieve

Genevieve P.

Hi @Mathijs Muller

Can you copy/paste exactly what formula you're using? It sounds like you'll have the TODAY function somewhere in your formula.

Keep in mind that the TODAY function needs an update or Save to happen to the sheet that houses the formula in order for it to recognize what the Date is Today. See this article where it states the following:

"Smartsheet will update all instances of the TODAY function in a sheet to the current date when any of the following actions are taken:

• You open the sheet and save it
• You add a new row to the sheet from a form submission
• You make changes to the sheet from an update or approval request
• You update the sheet by way of a report
• A linked cell updates the sheet
• You open the sheet being referenced in a cross-sheet formula "

If your formulas are on a separate sheet than your data, one of the previous actions will need to happen on this separate sheet.

I would suggest putting your formulas in to a Sheet Summary Field (see here) to keep it on the sheet where the new rows are created.

Then you can select Sheet Summary Fields as a source for Metrics in a Dashboard:

Let me know if this makes sense and has resolved the issue you were seeing!

Cheers,

Genevieve

Mathijs Muller

@Genevieve P many thanks. I now put all formula's on one data sheet, and my dashboard pulls all data from there. Indeed when I go into the data sheet and save it, the dashboard is working. Not an ideal solution, but it works with minimum effort.

Andrée Starå

Hi @Mathijs Muller

I hope you're well and safe!

To add to Genevieve's excellent advice/answer.

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!