Dashboard Template with help columns - please help

Options

Hi all,

I am attempting to use this dashboard template, https://app.smartsheet.com/r/template-gallery/templates/3c06021d-a78a-4203-a8ae-1282c8dc1555/Dynamic-Dashboard-with-Form-Filter?tg-locale=en_US. Note this includes the dummy data that comes with the template and I will be replacing it with my own info. There appear to be four primary sheets, noted below.

Intake Sheet

Region Sheet - this is where helper columns are. It looks like the region info feeds into the summary and metrics sheet but I am unclear how. Does the intake form feed IN to the region sheet or how is that data collected/setup there? Also, in this dummy data there are only 99 entries on the region (a mix of the same 4 region types) but more than 200 entries on the intake sheet - why the difference? Lastly, the last row of the region sheet has a red flag in the helper column - can you help me understand why? For the sample data they use 4 regions - with my real data I would list 7 departments

Portfolio Summary - appears to be the a duplicate of data from the intake sheet but only with key columns that are tied to metrics reported in the dashboard

Dynamic Metrics Sheet - this is where the data is for any filtered group that is then pulled into the dashboard.

I am confused on how to use the Region Sheet that has the helper columns

Lastly, please note that there is a form tied to the intake sheet and also a form on the dashboard so it can filter data.

Answers

  • Itai
    Itai ✭✭✭✭✭✭
    Options

    Hi @Kathryn Pineda,

    This seems to be a very good template.

    From my understanding the Region sheet is only to collect the filter region, every time you choose to filter on a different region a new line appears, the formula under the flag will turn the flag red (True) because it has the highest row number (As in this is the most recent filter entry through the form).

    This feeds into the Dynamic Region Metrics sheet using Index(Match()) and the formulas in this sheet will "fiter" the data in the intake sheet accordingly. This would feed into the dashboard like you mentioned above.

    To start using the template you need to activate the form in the Region sheet and add it to the dashboard web widget at the top as instructed on the dashboard.

    I hope I made sense, its one of those things that would be much easier to explain on a call :)

    Let me know if you have any more questions.

    Itai Perez

    Reporting and Project Manager

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    Hi Itai,

    Thank you for the response. What isn't clear is why are the four regions listed multiple times? When a new entry is made on the intake form there appears to be no interaction at all on the Region sheet - where is that connection? It feels like some component of the Region sheet is missing. How does it tie in to the Region Helper column on the Summary sheet?

  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    Hi all,

    I am attempting to use this dashboard template,  Note this includes the dummy data that comes with the template and I will be replacing it with my own info. There appear to be four primary sheets, noted below.

    Intake Sheet

    Region Sheet - this is where helper columns are. It looks like the region info feeds into the summary and metrics sheet but I am unclear how. Does the intake form feed IN to the region sheet or how is that data collected/setup there? Also, in this dummy data there are only 99 entries on the region (a mix of the same 4 region types) but more than 200 entries on the intake sheet - why the difference? Lastly, the last row of the region sheet has a red flag in the helper column - can you help me understand why? For the sample data they use 4 regions - with my real data I would list 7 departments

    Portfolio Summary - appears to be the a duplicate of data from the intake sheet but only with key columns that are tied to metrics reported in the dashboard

    Dynamic Metrics Sheet - this is where the data is for any filtered group that is then pulled into the dashboard.

    I am confused on how to use the Region Sheet that has the helper columns

    Lastly, please note that there is a form tied to the intake sheet and also a form on the dashboard so it can filter data.

    @Andrée Starå Would you be able to offer any insight into this by chance?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!