Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table: Mult Consolidation Ranges
I have an Excel Workbook with 2 sheets of data. Both sheets have identical
fields(column lablels). Basically, the only reason it is in 2 sheets is because there are too many rows for one sheet. I want to create a Pivot Table using the data from both sheets, but I want it to appear exactly like a Pivot Table from a single sheet. Meaning, when I create a Pivot Table of Multiple Consolidation Ranges of the 2 sheets, I do not have the same Pivot Table fields. I only have Column and Data fields. I want the Pivot table to have all of the fields that it would have if all the data were on one page. Is there something I am doing wrong? Or is it not possible to do this? Thanks, MT |
#2
|
|||
|
|||
You can create a Pivot Table from multiple consolidation ranges, but, as
you discovered, you won't get the same pivot table layout that you'd get from a single range. There's an example he http://www.contextures.com/xlPivot08.html If possible, store your data in a single worksheet, or in a database, and you'll have more flexibility in creating the pivot table. MRT wrote: I have an Excel Workbook with 2 sheets of data. Both sheets have identical fields(column lablels). Basically, the only reason it is in 2 sheets is because there are too many rows for one sheet. I want to create a Pivot Table using the data from both sheets, but I want it to appear exactly like a Pivot Table from a single sheet. Meaning, when I create a Pivot Table of Multiple Consolidation Ranges of the 2 sheets, I do not have the same Pivot Table fields. I only have Column and Data fields. I want the Pivot table to have all of the fields that it would have if all the data were on one page. Is there something I am doing wrong? Or is it not possible to do this? Thanks, MT -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
You can use MS Query and create a union query of the two data sheets so that
the data can be brought into the pivot table together. Gary "MRT" wrote: I have an Excel Workbook with 2 sheets of data. Both sheets have identical fields(column lablels). Basically, the only reason it is in 2 sheets is because there are too many rows for one sheet. I want to create a Pivot Table using the data from both sheets, but I want it to appear exactly like a Pivot Table from a single sheet. Meaning, when I create a Pivot Table of Multiple Consolidation Ranges of the 2 sheets, I do not have the same Pivot Table fields. I only have Column and Data fields. I want the Pivot table to have all of the fields that it would have if all the data were on one page. Is there something I am doing wrong? Or is it not possible to do this? Thanks, MT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating pivot table to include additional rows | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) |