Formulas and Sheet Saving

All of the sheets in my solution have a date column for "Today". =TODAY()

I use formulas in several other columns to determine if the checkbox column for Current Qtr should be checked. (same for Previous Qtr and Next Qtr)

I use the checkboxes in report filters.

Problem: Because my formulas are updating sheets daily, I have to save each sheet individually for them to update & show up properly in reports.

Assumption: Even though no one has been in the sheet:

- The formulas are updating the sheet

- The sheet does not get saved

- The sheet remains in limbo until someone clicks the save button.

Has anyone else run into this?

Is this a known issue?

Does anyone know a fix?

Find more posts tagged with

Sheets

Formulas

Reports

Conditional Formatting

Enhancement Request

Comments

Terri Pavlovic

I've run into a similar issue. There is a thread here https://community.smartsheet.com/discussion/sending-scheduled-reports-and-formatting

You are correct that TODAY() does not get calculated in the sheet unless you open it and save it. Reports do not do any calculations. I would gladly upvote for calculations in reports!

Part of the solution would involve linking your sheets so that you only open one master sheet each day that then updates the other sheets. I didn't try this. I've been telling our PMs to open and save their sheets every day when possible, but it's not a good long term solution.

Zack S

Hello Kris,

To confirm with Terri, formula will only be run when you open the sheet itself and reports won't automatically instrigate the sheets to update those formulas. I'll be sure to add both of your votes of support to our enhancement request list for our product team to reivew this.

As Terri mentioned, there is a workaround you can use where you link one master sheet to all the other sheets with a single cell link. You can then open the master sheet and change the value in that linked cell and that will push to all the sheets that cell is linked to, causing them to update their formulas.

-Zack