Report not updating formula unless the underlying sheet is opened and "refreshed"?

James Keuning

I have a report that brings in a bunch of sheets with one of those "this task was due 15 days ago" or "this task is due in two days" types of formulas. So this report has a due date and this text narrative.

I run the report, and the text is wrong. Like Due Date will show a date in the past, and the narrative will say that the item is due in two days. So I open the underlying sheet, and close it, and the report updates.

Is there something I need to do to make these formulas update?

Andrée Starå

Hi @James Keuning

I hope you're well and safe!

Yes, the sheets need to be "activated" and saved for the data to update.

You can use something like my workaround below.

Please 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!

Andrée Starå

Hi @James Keuning

I hope you're well and safe!

Yes, the sheets need to be "activated" and saved for the data to update.

You can use something like my workaround below.

Please 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!

James Keuning

That makes total sense. Looks like I am going to be busy adding columns and creating workflows for 20+ sheets. I suppose it's better than discovering this when I have 40+ sheets.

Andrée Starå

@James Keuning

Haha! Yes, I agree.

Here's more information. Also, there are other workaround options, but I find this to be the simplest one.

Usage Notes

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 (more on these alerts here)
• You update the sheet by way of a report (more on reports here)
• A linked cell updates the sheet (more on cell linking here)
• You open the sheet being referenced in a cross-sheet formula (more on cross sheet formulas here)

The following will not update formulas that are using the TODAY function to the current date:

• A report looking at the function in the sheet is opened.
• A dashboard showing information from the sheet is opened.

https://help.smartsheet.com/function/today

James Keuning

A linked cell updates the sheet (more on cell linking here)

You open the sheet being referenced in a cross-sheet formula (more on cross sheet formulas here)

This is interesting and almost makes me feel like I should have add a cross sheet formula or a linked cell in every sheet that I create. Just so that I can force updates if needed, rather than go back and add such a column to a bunch of sheets. I mean, that's a pretty simple thing to add to a template.

That said, this exercise of adding a column to a bunch of sheets is motivating me to accelerate my Python learning and use the API.

Andrew Stills

@James Keuning, I had a similar problem with the RGYB symbols not updating for reports unless the sheet had been opened. Although some users suggested adding a date to helper column and running an automation to update the date every night, this threw off my "Date of Last Activity" variable by making it look like every project had been opened every day.

@Ryan Sides (giving credit where credit is due) mentioned he had noticed toggling the Lock Row variable does not effect the auto populated "Last Modified On" column and suggested I try toggling the Lock/Unlock through a nightly workflow... it solved my problem wonderfully. No Helper sheet needed, no other dates getting interfered with, no changes to my sheets when it is completed.

Here's a screen grab with an example of the automation I now put into all my templates. Try it and see if it helps solve your TODAY() function issue... and please let us know!

Tim Starkey

@Andrew Stills and @Andrée Starå

First, these are both great solutions. Sure wish we had realized this when we got started in Smartsheet.

Question - Does running a control center global update (add a new column for instance) cause the TODAY function to update? My guess is no, because I have run a global update in the last couple days, but I have a report that a parent row that summarizes financials, and they all show last modified dates going back a month or more. BUT, now that I think about it... adding a column wouldn't modify that specific rows last modified date... so maybe will will trigger the TODAY function?

Reason I am asking... we have 445 projects. I run a financial report the first of the month, and have to manually go into the sheets to update and have it roll over to the new month. (In practice, I wait till the second or third of the month, by which time users and API updates have triggered it.) So, I will be adding an automation (similar to Andrews) to my template, and work through the 445 projects... but that will take time. So if I can run a global update (find a replace a single cell) that will buy me some time.

Like Andrew, having an entire column update with a new date will throw off my reports based on last modified... so I will be using the lock/unlock method if that still works.

I guess this leads me to a second question... since Global Updates do not allow me to update automations or conditional formatting in the sheet (I have put in THAT request a few times), is there a simple way to push new automations out to sheets using Zapier or similar? My guess is no, and will require an API beyond my current abilities.

Thanks, and sorry for hijacking this thread... seemed like the most relevant without having to post a new thread.

Andrew Stills

@Tim Starkey

I like your train of thought and only wish I could test your ideas, however, I'm stuck without control center access (company limitation). I specifically design my sheets to avoid needing to push these types of changes after the fact (I guess we all really try to do that).

My first question was, can you push changes into automations... but you answered that in the last paragraph. Same with conditional formatting... since I use conditions heavily on my data roll-up sheets to make reports more colorful and useful and to highlight different information than I would highlight in the project sheet. (A better fix here would be to allow conditional formatting on reports... which I submitted as feature request.) I've been able to work without control center for some time (3 years and counting) and these seem like pretty obvious wins for control center functionality. I'm not sure why I would subscribe without them. I'm doing templating just fine... it just takes time for new features to work their way into all projects if I don't deem them important enough to go add them.

Almost seems like adding a helper column and including a date/text to every sheet for potential future use might be a good strategy future development?

Tim Starkey

A linked cell updates the sheet (more on cell linking here)

You open the sheet being referenced in a cross-sheet formula (more on cross sheet formulas here)

@Andrew Stills @Andrée Starå Do linked cells still update a sheet if it hasn't been opened and saved? I had read in another post that the 'lock/unlock' workflow no longer updated the sheet after a recent update.

I had not implemented it yet, but intended to add a cell link in all my templates back to a single master "trigger" sheet. One that I could easily open and save. I might even be able to make an automation in that one sheet that would auto update the sheet (the record a date). Then I could easily change that one automation if I wanted to change frequency since control center does not update workflows across sheets.

But if the above concept does not work, then I will need to add a record a date automation in the template like you posted here

Daily Update Solution without the need to open the sheet, use Zapier, or similar — Smartsheet Community