Dashboard Widget Updates - by Week

Dee.M

I have created a Master Smartsheet which I am using to create my dashboard. My Master Sheet is displaying collated values for each week of the year taken from other reference sheets.

I have created the widgets for Week 49 which I believe will remain static until I manually update to display Week 50 values.

Can you advise if there is a way for these values to automatically update to Week 50, Week 51 and so forth on my dashboard.

I have attached a copy of same along with the formulas used for calculating the values.

Dashboard Widgets Week 49.PNG

Service Overview Master Sheet.PNG

AW Total Formula.PNG

Gen Value Formula.PNG

Paul Newcome

I would suggest creating some Sheet Summary fields. Then you can use formulas to pull the correct week's value into these. Referencing the sheet summary fields in your metrics widgets should provide for automatically updating widgets.

Dee.M

Thanking you Paul.

Unfortunately, I don't have the Sheet Summary feature based on the package I have.

I am a novice trying to create a simple dashboard that will update automatically .

I have created some summary fields on each sheet which for the most part are replicated in my Master Sheet but I still do not understand how these will update my widgets.

Do I need to modify the formulas to accomplish this?

Paul Newcome

Without the sheet summary fields, this would be my suggestion...

Move the calculations (formulas) onto the same row as the week number. Then indent each of the rows underneath of the very top row.

Next we insert a column (checkbox type) with a formula that will automatically check the box for whichever row should be displayed.

Then in this parent row we can use something along the lines of...

=INDEX(CHILDREN(), MATCH(true, CHILDREN([Checkbox Column]@row), 0))

This will pull the data from the row that is checked up into this top parent row.

If you point your metrics widgets at this row, then the data on the dashboard should update automatically.

Dee.M

Thanks Paul,

I have indented as advised and added a checkbox column but wouldn't know where to start to write a formula that would check the box for a particular row (e.g. Week 47) to be displayed - or what cell this formula is to be added to.

Could you offer guidance on this?

Checkbox Column.PNG

Paul Newcome

The formula is going to depend on a number of variables. I assume you are wanting "Current Week"? If so, you could use something like this...

=IF(WEEKNUMBER(TODAY()) = VALUE(RIGHT(Week@row, LEN(Week@row) - 5)), 1)

Dee.M

Thank you Paul, that worked a treat.

I appreciate your help.

Paul Newcome

Happy to help. 👍️