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.
test-wednesday.xlsm
* Pivot TablesMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Tom Farrar

8/22/2022 - Mon
Tom Farrar

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.
david matthews

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
Tom Farrar

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Tom Farrar

Another question you might ask yourself is why do you need the data on 13 different sheets?
david matthews

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?
Tom Farrar

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/
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
david matthews

ASKER
Really sorry tried that and got it to work using instructions and confused me. Is there a simplier way?
Tom Farrar

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.
Tom Farrar

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
david matthews

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?
ASKER CERTIFIED SOLUTION
Tom Farrar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
david matthews

ASKER
Im just so confused but a big thanks anyway and may look at going down macro road.
Tom Farrar

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.