Kpi's sheet that Auto counts from weeks to monthly but also sorts highest to lowest

For work I need to create a workbook to use to calculate different stores sales numbers per week as well as adding each week to the monthly list. I have tried a basic sum(sheet1:sheet4!c3) for the monthly sheet but this does not work for my situation because i also need to be able to organise them highest to lowest for presentations each week to see how each store is tracking. and due to the formula being cell specific any time i try to organise the weekly tables in their descending order it throws out the monthly reading. How would i go about changing the monthly formula to better calculate by each stores results and what grouping or any other changes would i need to do to my weekly sheets so this can work without being too overly complicated so i can pass this on to other managers who may not be AS handy with excel as i am.

Attach a workbook with a small sample and mock-up what you are trying to achieve.

Go Advanced -> Manage Attachments -> Upload

Here we go, so If i were to now oragnise week 1 & 2 in descending order of their total items sold it would change the totals for the monthly spread because its regestering individual cells not the actual totals for the seperate stores

Are you tied to the structure of your input data, ie. distinct weeks on separate tabs?

If you have all the sales data in one sheet summarising by month is easy.

thinking about it, i guess not. So youre saying that if its all on one sheet but input separately among weeks that we could have each individual week sort highest to lowest as well as a calculation for the month?

What about simply....
Date - Shop - Item
as your fields?

Then use pivot tables to organise into weeks and months as you like.

But this would then require a slightly more comprehensive understanding of excel to create the pivot tables each day/week would it not? the results we will be tracking need to be able to be updated daily and while i am getting a better understanding of pivot tables with all the tutorials i have watched there will be some people in the company who could not do this on a regular basis. I am designing this to be super quick and as easy as possible, but unfortunetly that means more work to start with. But thanks

Not really. You have all your data in a table that has a name.

You create the pivot table that has the table name as its source.

Input data changes, ie. table grows or data is changed.

Refresh the pivot table at a click of a button.

Voila.

The alternative is formulas, which I believe are more difficult to maintain, ie. they break without constant maintenance.

could you possibly provide an example in a separate sheet on the previously attached book? i must be thinking too far into it then (which is highly possible)

In the example if you add a row to the bottom of the sales table or change any of the data in the table, then select anywhere in the pivot table and click Data -> Refresh.

You can automate the refreshing of the pivot table using VBA if you wish, so any change to your data is immediately reflected in the pivot table.

Much appreciated. I should be able to take this and adapt it to my needs perfectly. Thank you