Dashboard Updates

Michael Lukson

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.

1. 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

Andrée Starå

Hi @Michael Lukson

I hope you're well and safe!

Please have a look at my post below with a method I developed to update the sheet(s) daily. This would probably solve all your issues.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Neil Watson

@Michael Lukson does converting the formula to a column formula solve the issue?

Andrée Starå

Hi @Michael Lukson

I hope you're well and safe!

Please have a look at my post below with a method I developed to update the sheet(s) daily. This would probably solve all your issues.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Michael Lukson

Thank you! I setup a HelperDate column to trigger as you outlined in your workflow post. I'll review and check tomorrow to see how it works. I'm still learning and figure there is a bit of usability knowledge I need to obtain to be able to produce and understand smartsheet. Still a learning process for me.

I'll check in tomorrow and see how it works.

Andrée Starå

@Michael Lukson

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Michael Lukson

Thank you Andree for the earlier fix on problem 1 that updated my dashboard widget and updated the corresponding sheet perfectly.

On problem two I'm still having the same issues.

Figure 1 - VLOOKUP Range

Every day I have to go in and edit the VLookup ranges to get them to list correctly on my lookup sheet. I'm going to explore pulling this data with a range, maybe I'm just not thinking the problem through well enough. My lookup sheet pulls the MAX() date (3/25/21 for this set of data). Then the other columns perform a VLOOKUP on that date and pull in whatever the balance is for that day. However, each day I have to redrag and set the VLOOKUP range for those column formulas as they will only remember what has been set from my previous edit. I've tried selecting the columns but that doesn't work with multi-column VLOOKUP. I've also tried dragging down a bunch of empty rows but it rubber bands back to the current data set.

Michael Lukson

Doing a report almost works. The problem is that I do a filter and say "Is In the last" "1" day. But that won't work on Monday's where the last reporting day is a friday. I don't see a way to filter for a max date in reports. Any tips or tricks to fix this issue?

Neil Watson

@Michael Lukson does converting the formula to a column formula solve the issue?

Michael Lukson

I was going to respond I wasn't able to do that but then I discovered the simple fact that if you hold down the control key you can multi-select columns! Before I would trying to click and drag and it wouldn't work. So I think your idea will do the trick!