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

  • Paul Newcome
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Dee.M
    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
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Dee.M
    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?


  • Paul Newcome
    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)

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Dee.M
    Dee.M ✭✭

    Thank you Paul, that worked a treat.

    I appreciate your help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!