Updating an aggregation sheet/dashboard with weekly data

megan.griffiths ✭✭✭✭
edited 12/09/19 in Formulas and Functions

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 



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!