Formulas not updating when new data is added to underlying sheet

When new data is added or updated to the underlying sheet, the formulas are not recalculating. I run an upload workflow each morning and want the metrics sheet below to recalculate based on the updated report I attach. I have to manually re-enter the formula to get an updated metric. The counts, averages and formulas using 'Today' all have to be manually recalculated.

How do I ensure formulas will update as the underlying sheet does?

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Jeff Reisman

@Ashlynn I think this might be caused by using the TODAY function in the formula. The TODAY value in a sheet tends to not update unless the sheet itself is opened manually by a user.

The common method to get around this is to create a "CurrentDate" date-type helper column where you can store the current date, and use a daily scheduled workflow each early morning to record the date in that column. I usually set it to put that date on every row where my primary column has a value, and then in my formulas instead of TODAY I use CurrentDate@row. So in your case that would be < (CurrentDate@row - 7)

Paul Newcome

Another possible reason could be the range. When creating the range, be sure to click on the column header instead of selecting a specific set of cells. Selecting the column header will ensure new rows are included as they are added to the source.

Ashlynn

Thanks to both of you @Paul Newcome @Jeff Reisman!! Appreciate the feedback - good things to keep in mind as I get my bearings in smartsheet.

Everything started working as it should when I ran the upload workflow with an attached CSV file rather than XLSX file. Not sure why this would cause issues...

Awalshga

Hi, I have a related issue that I just discovered. I track project phase start and end dates on a master project list. I created helper columns to calculate the start month, quarter, year and Qtr/Year. I did the same for the phase end date. For some reason my helper columns are not updating on some rows. If I edit the start month on those rows then just hit enter (i.e. no changes made), the helper columns are updated correctly. I do use data mesh to update the start/end dates for some projects from their underlying project plans but this should not really matter from what I can see. Any insights would be appreciated. Thx!

Paul Newcome

@Awalshga Do you have the formulas set as column formulas or cell formulas?

Awalshga

Hi @Paul Newcome thanks for the response. They are column formulas.

I figured out the issue. We use a sheet to collect certain data (including phase start/end dates) from each project plan. We have a report that pulls all of those collector sheets and we Data Mesh that info up to a master sheet. I discovered that the date columns in the collector sheet were set to be Text/Number format rather than Date format 😣. Data Mesh was working but it was putting a text-formatted date into the master sheet so the formulas that relied on that date value were not working. If we edited the cell in the master sheet without making any changes, SS would convert it back to a date and the helper columns suddenly worked.

So...we went into each of the collector sheets, changed the format on the columns to Date, then remapped the Data Mesh and all is working. We won't make that mistake again :). Appreciate this community and the support it provides.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.