Formulas Not Automatically Updating

Laura McDonald

Hi,

I have a sheet with formulas in multiple columns. Non-licensed staff are able to add rows of data via a form, and also make updates to existing rows of data via a dynamic view. They view the data via a report. I have this same set up running for 33 separate locations, however one location has been indicating for the past few weeks that their formulas aren't updating after they make a change to the data. The only fix I've found is for me to open the sheet, then the formulas immediately update, but this isn't an ideal solution for them to have to rely on me to open the sheet for them each time they make a change to the data. There are no TODAY formulas in the sheet, it's mostly just SUM formulas.

Does anyone know why it wouldn't be automatically updating formula calculations unless I open the sheet?

JamesB

@Laura McDonald I had a similar issue on a a sheet I use to create reference dates for other sheets. To get around this I used an automation on the sheet to update a hidden column with data at 3am everyday. This action caused an open/close of the sheet that triggered the formulas to update. I would imagine you could do something similar with an automation to trigger when rows are added, which is anytime a form is submitted.

Laura McDonald

In my situation, a hidden helper column that updates the data at a specified time each day won't work unfortunately. I need the formula to recalculate and update in real time (understanding there could be a minute or 2 delay) as users are inputting new inventory totals, and updating inventory totals when there's an error, and then going to the report to verify their updates are successful and have corrected the outstanding outage. They need to see this update more quickly than waiting until the next day. The sheet was built via a Control Center template, and I have 32 other sheets built from the same template that are working fine, it's just this 1 sheet that isn't updating the formulas consistently.

Bassam Khalil

Hi @Laura McDonald,

please try the following solution for daily sheet update created by @Andrée Starå , i try it form similar issue and it's work perfectly.

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