Updating an aggregation sheet/dashboard with weekly data

megan.griffiths

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! 

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 

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​

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

megan.griffiths

Thanks Paul  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 Newcome

Happy to help! 

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).

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.

 

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

Andrée Starå

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