Dashboard Widget Updates - by Week
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.
Answers
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you Paul, that worked a treat.
I appreciate your help.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!