Pivot Tables using multiple tables as a source

I'd really appreciate if someone could help me with this, I have spent quite a bit of time looking on other forums, but haven't manage to find the answer.

Basically I want to use the basic pivot table function in Excel 2010, but rather than select the data form one table, I would like it to select it from multiple tables that all have the same headings.

I have four tables with the following headings, and basically I want the pivot table to regard the information in them as one long "table" of information.

Date Ref Account Description Gross VAT Net

I have tried populating a separate work sheet with all of the information from the other four tables, however, this is quite a clumsy way of doing. It means that if and of the information in the four tables is moved about, the new worksheet gets muddled up. It also means that I have to limit the length of the other four tables.

I have also tried using Excel's consolidate function, but I haven't managed to get this to work how I would like it to.

Once I have the Pivot table referencing all the data; I intend to filter the pivot table by the "Account" column; set the row labels as "Ref", followed by "Description"; and set the "sum of values" to the sum of the "net" column.

Thanks in advance to anyone who can help.

Regards

Chris

Hi XOR LX,

Thanks for your help.

I have tried this before as well, however the Pivot table doesn't retain the data fields of: -
Date Ref Account Description Gross VAT Net

Instead it uncomprehendingly (to me) creates four new fields: -
Row Column Value Page1

Row has the "Accounts" information in
Column has a mix of data in
Page1 has Item1, Item2, Item3 and Item4 in - no idea what they are
Value has all information except "Accounts"

That would be great. I have uploaded the file to http://www.megafileupload.com/en/fil...bles-xlsx.html

The four tables the data will be entered on in Journals, bank receipts, bank payments and sales. The Data test sheet is where I have tried manually consolidating the four tables, however this is quite clumsy. The Multiple Consolidated Ranges sheet is where I have tried your first suggestions. The rest of the sheets I am hoping will be generated using Pivot Tables.

Thanks again

Chris

Thanks Izandol.

The problem is I only want certain data to be entered on certain sheets. I don't have any knowledge on how to code a record set, will want I am trying to do be possible? It doesn't seem as though I am expecting that much out ok Excel!

Chris

Thanks. I have played about with the wizard quite a bit, trying with and without the header row, and using and not using the Create a single page field for me option, without any success.

I have just tried without using the Create a single page field for me option, and with the header row, and I am still not getting what I am looking for . When not using the Create a single page field for me option the wizard does mention selecting the number of fields I would like, but there is only a maximum of 4 allowed, and I don't seem to be able to select any data for these fields.

Thanks for this. What I'm after I think is only shown on the "Sales 60.10" tab. The pivot table is only showing data with for the items with "Sales" in the accounts column. The rows of the table are first grouped by the "Ref" column, and then by the "Description" Column. The only column in addition to the row's description is the "Sum of Net" column, which shows the sum of all items with the same "Ref" and "Description.

I am hoping to "stack" the data on top of each other into a single table. Is this possible?

Thanks for all you help

Chris

Hi XOR LX

Sorry I haven't replied sooner. Here is a more stripped down version of what I would like:

TEST HA WP V2.xlsx

I want to enter the data in the "tables" on sheets "Journals", "Bank Receipts", "Bank Payments" and "Sales Day Book", and I want to generate a standard Pivot table from this data. All of the "tables" on the separate sheets have the same column headers. I want the fields of the Pivot table to be the headers of the columns of the "tables". I can get this to work if I just set the pivot table to generate from one table, however, I don't seem to be able to get this to work for all of the data in all 4 tables.

In the "Data Test" sheet I have manually "stacked" all of the data from the 4 separate tables, and then on the "Sales" sheet I have generated the pivot table I ultimately want to produce. This is quite a clumsy way of doing. It means that if and of the information in the four tables is moved about, the data on the "Data Test" sheet would get muddled up. It also means that I have to limit the length of the other four tables.

Thanks again for your help

Regards

Chris

Would anyone be able to help me with this?

Thanks

Chris

Is there a reason you are not using the free PowerPivot add-in? It is purpose made to do what you want.

I don't have the time to do this, but you can create linked tables in PowerPivot from your data, create a lookup table to allow relationship to be formed and then create a single pivot table with data from all of the tables combined.

Hi XOR,

Thanks for getting back to me, and sorry I have taken so long to respond. The tables need to be on separate sheets as there will be additional columns on the separate sheet, with the extra columns on the different sheets all being different. Each of the separate sheets will also be printed off and used in their own right.

Thank you for the recommendation minnesotaart, I'll look into it!

I have had a play around with PowerPivot, and I haven't been able to see how I can get it stack/consolidate the 4 tables.

Any help on getting Excel to stack/consolidate the 4 tables would be really appreciated.

Thanks

Chris

Powerpivot wont really help with what you are trying to achieve. The only thing I can think of is to have a macro that consolidates the data into a separate table and use that as the pivot table data source. If you would like to retain the pivot table (and not recreate it each time you want to update it), perhaps you could write the macro as an ad hoc data refresher.

Thanks Matlennon

Would this be as relatively basic macro to write, and is there anywhere you would recommend to go to learn how to write basic macros?

Thanks

Chris

Can anyone help with this?

Thanks

Chris

Can anyone help with this?

Thanks

Chris

Thanks for the link