I've built my first company dashboard and I've mostly been able to work around any issues I've discovered while building it. However, my dashboard has issues updating that I am trying to work around. The way I've built it requires me to basically rebuild the dashboard every morning in part. From the best I can tell this has to do with how smartsheet updates formulas and builds charts based upon daily changes. I will outline these two issues.
- Updating Formulas. From what I can gather smartsheet doesn't update formulas until you open the sheet. As an example I have a simple sheet built to track days until accounting close with a heat map (color).
Figure 1 - Base Close Date worksheet with countdown formula
Figure 2 - Closing Date - Next Sheet
As you can see in Figure 1 it has a DaysDue (i.e. countdown) of days until this date. I have a Metric Widget on my dashboard that pulls the most recent date. It does this from a second sheet (Figure 2) which uses a MIN formula to grab the next date. The problem is that the first sheet (Figure 1) won't update the DaysDue until you open both worksheets to refresh the formula. Is there anyway to automate this so the dashboard user doesn't have to manually go through this process every day just to get the dashboard widgets to update?
Number 2: Chart with VLOOKUP issue.
I have a sheet where I enter daily bank closing balances and a chart which is designed to update the balances and pull the most recent balance for three accounts into a 3 bar chart. After entering the sheet info (i.e. new balances) I run into a range definition problem for the chart. In figure 1 you can see the vlookup formula which grabs the current balance info based upon the date value in the Date column. In figure 2 you can see a corrected range. The problem is that to get this chart to work on the dashboard I have to go into the sheet which pulls the most recent data (with the VLookup - Figure 1) and then open the reference and manually drag a new reference range for the lookup. It doesn't automatically scale with new entries. Is there other technique I might be able to use that might get rid of the need to manually rebuild this sheet daily for use in the dashboard?
Figure 1 - Sheet with Vlookup to grab the most recent balances
Figure 2 - Chart Range selected
