Formula columns not updating automatically after data is pulled in via datamesh

anna_ashby

We discovered that our formula columns are not updating automatically after data is pulled in via datamesh and they only update if someone goes into the sheet, clicks into some cells, then saves the sheet. Support told me to set up an automation on the sheet that does something like locking then unlocking a specific cell to trigger a recalculation of the formulas, but after a few months of trying different methods, none of them seem to work. Has anyone else had this experience, and found a better workaround that doesn't involve me going into each of the 8 sheets every day to save them manually? Some background on the sheets in question- they have 2115 rows and 127 columns (60 of which are formula columns), and they have already been broken out into multiple sheets to try to reduce number of columns, so that's not an option.

Georgie

Hi @anna_ashby,

It sounds like your formulas might contain the TODAY Function. This function is only updated when certain actions are taken, such as opening the sheet and saving it - you can see a full list of the actions that update it within the help article I’ve linked.

However, there are several options for automatically updating the TODAY function, all of which can be found in this help article: Automatically update the TODAY function in formulas.

Hope that helps!

Georgie

anna_ashby

Thanks for your reply Georgie! I am going to try one of the automation setups from the article and see if that works.

Georgie

@anna_ashby,

No problem! Hope that solves it - let us know if not!

Georgie

anna_ashby

@Georgie I tried a few automations, and nothing has fixed the issue unfortunately. Even with an automation running, it doesn't update and save formula changes without me going in manually. The only other option on the help article was using Bridge, which we haven't purchased so I can't try that. I'm thinking of trying to setup a Power Automate flow to go into the 8 sheets a few times a day to update and save them. We never had an issue with this for many years on these sheets, so it's just strange to me that I need to setup an elaborate workaround now.

Georgie

Hi @anna_ashby,

Sorry to hear that you’re still experiencing the issue - it’s strange that you’ve set up an automation and it hasn’t resolved the problem. It appears that something is causing this problem on the specific sheet, so I’d recommend creating a new support ticket so that the team can review logs for your specific sheet and determine the cause for this.

When you create the new ticket, if you can provide the following, that’ll help the team to investigate:

• A full screen recording showing that formulas are not updating without opening the sheet and saving it, and including a view of the activity log (with date range set to when you set up the record a date automation to now), and a view of the cell history of several affected cells.
• The sheet ID (open the sheet and click File > Properties to find the ID).

Thanks,

Georgie

anna_ashby

@Georgie

Update: I created copies of the sheets to test a few different scenarios. The issue still occurred when I tested without changing any variables but resolved when I reduced the number of rows on the sheet by half (~2000 rows reduced to ~1000 rows). This explains why we never had this issue back when we had fewer rows. I think the solution will have to be splitting the target sheets to reduce the number of total rows unless Smartsheet has a fix to formulas not automatically refreshing on sheets over a certain number of rows.