Link to home
Start Free TrialLog in
Avatar of david matthews
david matthews

asked on

How do I create what I believe is pivot tables to be able to do running total from 13 sheets.

Hi I have a workbook of which has 13 sheets and I want to keep a running total for all sheets.

Ive been looking on internet and believe it a pivot table.  Don't know where to start as not that far advanced.  Let me explain what I need to do.

In sheets 1-13 the layout will be the same as in column headings etc.

in the sheet called "report"  I need it to show a running total in the columns as follows..

If in sheets 1-13 in column B shows the word "CUBIC" , "SHERE" "CLB" "UNDER £1"  I need the data from columns B,C,D,F carried over into a running total on the sheet called report.

If in sheets 1-13 in column B it does not show the words as shown above I need the data from columns B & F copied over into a running total in the sheet called report.

If it means doing it in 2 separate tables that's OK as long as they are both on the sheet called report.

Please find enclosed copy of worksheet.
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

You said:

"In sheets 1-13 the layout will be the same as in column headings etc."  - Not true in the example.

A pivot table is probably not the best solution working from 13 separate sheets. The sheets need to be combined in some manner.  The most logical way (in my opinion) is to use Power Query.  Need a version of Excel >= Excel 2010.   Power Query will leave the data on the 13 sheets and consolidate it in the background.  Then from the consolidated table you could create a pivot table.
Avatar of david matthews
david matthews


Apologies for that Tom  my fault.  They will be all the same when I do final workbook.

Im using excel 2016.  

Power query  whats that ?? never even heard of that.  sorry
You have "Power Query" embedded in your Excel.  In 2016 it is called "Get and Transform".  It is an ETL (extract, transform and load)  tool that enables you to modify, merge, etc data.  Its strength, among other things, is that it records the process like a macro (but way better) so it is usable over and over as data changes in the source.  There are many videos on the internet if you google "power query" excel 2016.  If you find it as an option for what you are doing perhaps I can lead you through the process.
Another question you might ask yourself is why do you need the data on 13 different sheets?
Its data for financial periods of 4 weeks each and need to be seperate.

Looking on excel under data tab I got   tabs  from access/from web/from text/ from other sources.  Am I looking in right place?
Move over a little to the right where it says "Get & Transform".  I believe you would be working from New Query, Blank Query.  Take a look at this:
Really sorry tried that and got it to work using instructions and confused me. Is there a simplier way?
You could load each of the sheets as queries and then merge them together.  Assuming your sheets change on a periodic basis, the merge would pick up the new data.  This again would be through Power Query, but the process is much easier.  Excel has a consolidation process, but I am not sure how well it works with 13 sheets, and it may not give you the flexibility you would want.  I will think about other possibilities over the evening.  Let me know your thoughts.
Attached is the example of how it could be combined with Power Query.  The consolidation tab is a combination of the four tabs.  You can add or change data to the four source files and refresh the consolidation table and the pivot table and both will update.  This is how it works using the append option in Power Query.
Yes that sort of what im looking for and just playing around with it and deleted the data  apart from the top rows where the headers are.and input my own.

Tried to go onto consolidation tab and went onto table and right click and refresh and comes up with intinalization of data source failed.

Any ideas?
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Im just so confused but a big thanks anyway and may look at going down macro road.
I hear you.  Don't give up in the long run on Power Query.  It isn't that difficult once you go through the steps I attempted to explain.  The example I gave you with the 4 sheets took me about 10 minutes to create the data and the consolidation/pivot table.  I would be happy to do it for your 13 sheets if you wanted to share.  But at some point you would need to understand.  - Tom