Updating an aggregation sheet/dashboard with weekly data

megan.griffithsmegan.griffiths ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
08/01/19 Edited 12/09/19

Hi all,

I'm struggling to think of a way to aggregate data that changes weekly. I will try and describe as best as I can! smiley

We have a sheet that financial advisers fill in weekly (separate sheet for each adviser) to show various information as attached in the screenshot. The adviser updates this weekly every Friday, and adds a new row each week as per the screenshot example.  

I then want to aggregate the data from all 30 advisers, on a collation sheet, that counts the total number of e.g. Value £, appointments held, proposals etc. The problem is that I have to do this manually every Friday, as I can't find a way to generate that collation automatically as the initial data sheets change weekly, adding rows as they go along.

The second problem I then have, is that the dashboard will only pull data from the line selected, so I would therefore need to amend the dashboard data every week, for it to pull through the new aggregated line.

Hope this makes some sort of sense?

Would appreciate any help at all.

Thanks, Meg 

Capture.PNG

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Meg,

    You could use cross-sheet formulas to automate it and collect everything on a so called master metrics sheet. That could then feed the dashboard with information. 

    Would that work?

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting​

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You could also START from the master sheet. Create a sheet that will be populated by everyone, but create a form for each adviser. THe adviser would submit their own form weekly with the same information that they are already populating, and everything would automatically be in one sheet.

     

    Pulling metrics from that master sheet would also be much easier.

    thinkspi.com

  • megan.griffithsmegan.griffiths ✭✭✭✭✭

    Thanks Paul smiley I have created a form, that populates a master form, which then feeds into the dashboard... Thanks for your help - never thought of using a form for this!

    Meg 

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    Something to keep in mind...

    The biggest determining factor between mine and Andree's solution is how much you already have in place.

    If you already had a lot of data recorded and a major change would have meant a lot of work for everyone, I would suggest Andree's solution. That way you avoid having to re-enter a lot of historic data manually.

     

    I just know that for me personally it is much easier to start with the "master" and then break down from there.

     

    At the end of the day what REALLY matters is what works for you (as long as it works hahaha).

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Paul,

    Good point!

    I just assumed there were a lot of data and that it would be more work to start from scratch.

     

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Thanks! I honestly figured there was already a lot of data as well, but I figured it couldn't hurt to throw the other option out there just in case.

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    You can never have too many tips/tricks, workarounds or methods!

    Give

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.