Put formulas in a calculation sheet or in source sheet?

Adamcain62

I just want to make sure the following scenario doesn’t have any issues - searched around but wasn’t conclusive...should I expect the dashboard to always be current in the following scenario?

1. Forms bring user data into a sheet.
2. A calculation sheet looks for daily totals using the Today function (among other things) for various items, as well as sums up historical entries
3. Dashboard displays data from both the source sheet and calculation sheet in charts/graphs, etc.

If no one actually opens up the source sheet or calculation sheets, will the dashboard always show the most current data? (not concerned with the 10 minute refresh rate)

thanks!

Genevieve P.

Hi @Adamcain62

The quick answer is yes, with your current set up it should show the most current data... however it does depend on what type of formula you're using with TODAY, and how often the form submissions are coming in.

The concern is that the TODAY function requires some sort of update to the sheet in order for the formula to recognize what "today"s date is. The most common way to make sure the formula is reading TODAY is to open the sheet, however a Form Submission effectively refreshes the sheet in the same way.

This means that whenever there is a new entry it will update your TODAY formula and the information will be updated in your sheet, then subsequently updated in the dashboard, without you needing to open the sheet at all.

That said, if your formula needs to calculate Today's Date on the days when there are no new form entries, one of the other ways to update the sheet will need to happen in order for the data to be correct. The TODAY Help Center article has a list of all the ways to update the Today function: https://help.smartsheet.com/function/today

Let me know if this helps, or if you have more questions!

Cheers,

Genevieve

Genevieve P.

Hi @Adamcain62

The quick answer is yes, with your current set up it should show the most current data... however it does depend on what type of formula you're using with TODAY, and how often the form submissions are coming in.

The concern is that the TODAY function requires some sort of update to the sheet in order for the formula to recognize what "today"s date is. The most common way to make sure the formula is reading TODAY is to open the sheet, however a Form Submission effectively refreshes the sheet in the same way.

This means that whenever there is a new entry it will update your TODAY formula and the information will be updated in your sheet, then subsequently updated in the dashboard, without you needing to open the sheet at all.

That said, if your formula needs to calculate Today's Date on the days when there are no new form entries, one of the other ways to update the sheet will need to happen in order for the data to be correct. The TODAY Help Center article has a list of all the ways to update the Today function: https://help.smartsheet.com/function/today

Let me know if this helps, or if you have more questions!

Cheers,

Genevieve

Adamcain62

Thank you so much - that helps clear it up. So it seems that as long as someone is submitting content into the source sheet, and as long as a I have a formula in the calculation sheet that changes based on the new content coming into the source sheet (not using the TODAY function, like say a running total of one of the column in the source sheet), then everything should be good. I checked out the linked content as well. thanks again!

Genevieve P.

No problem! If you're experiencing any issues in the future with this, you can either post here again or Contact Support, depending on the type of issue. 🙂