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.
test-wednesday.xlsm
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.
test-wednesday.xlsm
ASKER
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
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?
ASKER
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?
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:
https://trumpexcel.com/combine-multiple-worksheets/
https://trumpexcel.com/combine-multiple-worksheets/
ASKER
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.
EE.xlsx
EE.xlsx
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
"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.