Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to source data from multiple worksheets in a PivotTable?
I am trying to use 3 worksheets in my "Range" during step 2 of 3 for the
PivotTable Wizard. I HAVE tried to use the multiple consolidation ranges option but I keep getting junk in my PivotTable along with error messagess that the data is truncated. Each of the three worksheets has 199, 102, and 159 columns of field names, respectively, which is why I can't combine all the data into one worksheet (or one table in Access, which is where the data originates). This also prevents me from creating a named range for a 3-D reference (since the number of columns are not the same) as the source. Even if each worksheet ahd the same number of columns, I still doubt that the PivotTable Range would take a 3-D reference named range. I would love to do this programmatically rather than with the PivotTable wizard but I still don't see how it is possible. If there are any PivotTable gurus out there, here's your chance to shine! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to source data from multiple worksheets in a PivotTable?
Hi Wally,
Unfortunately to my knowledge you can not combine data from seperate origins into one table even if they have the exact same headings. 2 solutions are 1) Create several pivot tables far away somewhere, one on top of each other, and then create a single pivot table based on these. 2) Using the MS Query facility in Excel create a query getting all you source data into one worksheet and then basing your pivot table on that. HTH J "Wally" wrote: I am trying to use 3 worksheets in my "Range" during step 2 of 3 for the PivotTable Wizard. I HAVE tried to use the multiple consolidation ranges option but I keep getting junk in my PivotTable along with error messagess that the data is truncated. Each of the three worksheets has 199, 102, and 159 columns of field names, respectively, which is why I can't combine all the data into one worksheet (or one table in Access, which is where the data originates). This also prevents me from creating a named range for a 3-D reference (since the number of columns are not the same) as the source. Even if each worksheet ahd the same number of columns, I still doubt that the PivotTable Range would take a 3-D reference named range. I would love to do this programmatically rather than with the PivotTable wizard but I still don't see how it is possible. If there are any PivotTable gurus out there, here's your chance to shine! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to source data from multiple worksheets in a PivotTable?
See if this helps
http://edferrero.m6.net/Pivot20032.html#4 Matt "Wally" wrote in message ... I am trying to use 3 worksheets in my "Range" during step 2 of 3 for the PivotTable Wizard. I HAVE tried to use the multiple consolidation ranges option but I keep getting junk in my PivotTable along with error messagess that the data is truncated. Each of the three worksheets has 199, 102, and 159 columns of field names, respectively, which is why I can't combine all the data into one worksheet (or one table in Access, which is where the data originates). This also prevents me from creating a named range for a 3-D reference (since the number of columns are not the same) as the source. Even if each worksheet ahd the same number of columns, I still doubt that the PivotTable Range would take a 3-D reference named range. I would love to do this programmatically rather than with the PivotTable wizard but I still don't see how it is possible. If there are any PivotTable gurus out there, here's your chance to shine! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what types of source data can't be grouped in pivottable | Excel Discussion (Misc queries) | |||
chart with data source from multiple worksheets | Excel Discussion (Misc queries) | |||
Dynamic PivotTable Data Source | Excel Discussion (Misc queries) | |||
Change external data source for multiple worksheets | Excel Discussion (Misc queries) | |||
Select different source data for a PivotTable | Excel Worksheet Functions |