Refresh/recalculate sheet automatically

CycleBagEd

Hi. I have date based functions in my sheets. These are volatile and respond to the current date and time.

I've found that sheets need to be opened and saved before the data is recalculated for these functions. This is a problem. I need the data to be accurate.

I suspect I can add a column which refreshes hourly and inserts the date and time of the refresh and this will probably trigger a save and recalculation - however this seems like it should be unnecessary.

Before I try this out, is there a way of scheduling a refresh for all sheets in a workspace?

thanks

Ed

Paul Newcome

There is not. You will need to go the automation route on each sheet.

Another option would be to set up a sheet that is strictly the TODAY function with the automations on it and then reference this sheet in all of your other sheets.

Andrée Starå

Hi @CycleBagEd

I hope you're well and safe!

This might help!

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

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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

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

Paul Newcome

There is not. You will need to go the automation route on each sheet.

Another option would be to set up a sheet that is strictly the TODAY function with the automations on it and then reference this sheet in all of your other sheets.

CycleBagEd

Thanks for the quick reply @Paul Newcome

I will add automation to my template. I will submit feedback as a feature request. I would expect volatile functions to calculate in the background on a platform such as this.

thank you

Ed

Andrée Starå

Hi @CycleBagEd

I hope you're well and safe!

This might help!

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

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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

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

CycleBagEd

Thanks @Andrée Starå

I've already set up an automation column to do something similar, at 6pm and 4am I ask the automation to "record a date" which inputs the date into a column, I'm not referencing an on-sheet date because there doesn't seem to be a need, it's there as a timing option in the workflow/automation options.

Hopefully this is causing the re-save of the sheet that's needed to trigger calculations with volatile functions.

thanks

Ed