Which underlying sheets need refreshing for report to be accurate?

Eva M

Hello,

We've been having issues at work regarding setting up automatic report emails where the recipients receive reports with old data.

Is it still true that if I have an underlying Roll-Up sheet (made from a Master sheet where all data arrives) based on which I have a Report - I must "touch" the underlying Roll-Up sheet in order for the corresponding Report to update?

What about if I have a Report that is generated from an underlying Master sheet directly without a roll up as a mid-step? Do I have to login and "touch" the Master sheet where all the data arrives from the forms?

Thank you!

Genevieve P.

Hi Eva,

If your Report is pulling from a sheet where form submissions come in, the Report should automatically update when new form submissions create rows in the source sheet without you needing to "touch" the sheet at all. I've just tested quickly and confirmed that this is the behaviour I'm seeing.

Are you using the TODAY function at all? TODAY would require some sort of "touch" on the sheet, but that touch could include a Form Submission updating the sheet.

Is it possible that there is something in your Report Criteria that isn't updating before the emails are going out? Could you post a screen capture of the underlying sheet in Grid View & of the Report Builder with the criteria showing (but please block out any sensitive data)?

Thanks!

Genevieve

Eva M

Hi Genevieve,

Thanks so much for the response! I do have the TODAY function in the Roll Up. When you say "that touch could include a Form Submission updating the sheet", what do you mean by that?

Here is a screenshot of the Roll-Up sheet with a formula showing that pulls the relevant data from the underlying Master sheet. I then basically make a copy of the Roll-Up in Reports. This Roll Up sheet is the item that needs to be touched.

Here is a screenshot of the report itself - basically a copy of the Roll-up:

And here below is a snip of the Master Sheet based on which I create the Roll up:

The Master sheet receives form submissions from over 70 units in different hospitals and encompasses around 40 columns out of which I create that Roll-Up.

It would be a huge help if the Roll-Up would update each time the Master sheet receives a form submission.

Thanks so much!!

Genevieve P.

Hi Eva,

Thank you for this explanation! So the Report will update automatically when the sheet updates... however what's stopping your sheet from updating every day is the TODAY function. There are a number of ways to update the Today function, but essentially something needs to refresh/open/trigger the sheet on that day in order for it to be able to recognize what date "Today" is.

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 this rollup sheet from a form submission
• You make changes to the rollup sheet from an update or approval request
• You add new data or update the content of the rollup sheet through a report
• A linked cell updates the sheet
• You open the sheet being referenced in a cross-sheet formula (this would be your last screen capture, the Master Sheet)

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.

What you could do is have a row at the top of your Master, intake sheet. Then in one of those cells you could have a formula that just counts the number of rows submitted. Then if you cell-link this cell from your Master sheet into your Rollup sheet this will help update your TODAY function.

When a new submission comes into your Master, this will update your COUNT formula in the cell at the top of the sheet. This cell-link will then update into your Rollup sheet, which in turn will update the TODAY function, rolling through all the way to the Report. Does this make sense?

Cheers!

Genevieve