Why is a formula not triggering unless someone opens the sheet?

jmhoward

Hi there,

I have 150 sheets that individuals use as time sheets. The sheet has rows for all the dates for a quarter. I have a "Current Month?" check box column in the sheet that checks the cell if the row date is in the current month. It uses this formula:

=IF(MONTH(Date@row) = MONTH(TODAY()), 1)

I have another check box column called "Previous Month?" that is checked if the row date is in the month prior. It uses this formula:

=IF(MONTH(Date@row) = MONTH(TODAY(-30)), 1)

I use these two hidden columns to filter two reports, one for the current month and one for the previous month.

Users are supposed to work out of these two reports rather than the sheet. However, when January turned to February, the check boxes did not switch until someone opened the sheet. So, if I haven't opened the sheet yet and it's February 3, the Current Month report still shows January dates.

Is there a way around requiring someone to open the sheet for the check boxes to switch automatically? Would a workflow be better instead of the formula? I'm trying to avoid having 150 users have to open the sheets at the beginning of the month just so the check boxes update. They should focus on the reports.

Many thanks!

JamesB

@jmhoward

I had a similar issue and use an automation. This action will cause a virtual open/close of the sheet which will update the formulas in the sheet.

I created a column named date. Then I used automation that runs everyday at 2am. This automation records a date in the date column I created.

Andrée Starå

Hi @jmhoward

I hope you're well and safe!

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

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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JamesB

@jmhoward

I had a similar issue and use an automation. This action will cause a virtual open/close of the sheet which will update the formulas in the sheet.

I created a column named date. Then I used automation that runs everyday at 2am. This automation records a date in the date column I created.

jmhoward

Thanks @JamesB ! So I just need to create a Date column and then set the automation to record the date daily? And that's enough to act like an open/close?

If that's correct, this is a terrific workaround.

Andrée Starå

Hi @jmhoward

I hope you're well and safe!

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

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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Pauline J

@ https://community.smartsheet.com/profile/Andr%C3%A9e%20Star%C3%A5 This looks like a great suggestion, along with your inputs on another post, recommending a Date Helper sheet that updates to the current date every night via automation. What is not clear to me is — when the helper sheet autoupdates, does this mean that any other sheet or sheets that are referencing the helper sheet will update without being opened themselves?

Thanks!