| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table-Multiple Consolidation Ranges
Hello. I am trying to create a pivot table using data from multiple
spreadsheets in Excel 2000. I have the table created. However, I am only given the choices of "Row", "Column", and "Value" as my selection choices. Normally, in a pivot table, I can move a column such as Quarter to the left, then also select Line of Business, within that quarter, then get subtotals based on lines of business in their respective quarters. But now what happens in this consolidated range pivot, they list the Quarters, then the LOBs under the quarters. Way I need it to look: Q1 Northeast Southeast Subtotal Q2 Northeast Southeast Subtotal How it looks now: Q1 Q2 Subtotal Northeast Southeast Subtotal I have even tried to use the page function to put the Quarter at the top of the page, but it doesn't let me select a particular quarter, only gives me the field name. Does anyone know if it's possible using this consolidated feature? I know I can if I base the table on one sheet, rather than multiple sheets. But I can't seem to get it to work the way I need it to. Also, I cannot just combine all the sheets to one on a "data" tab since the information on each tab gets updated/added to each week. Thanks for any assistance you can provide! Rach |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table-Multiple Consolidation Ranges
You can create a pivot table from multiple consolidation ranges, but the
results may not be exactly what you want. There's more information he http://www.contextures.com/xlPivot08.html Perhaps you could create a macro to combine the data onto one sheet after the weekly updates. Rach wrote: Hello. I am trying to create a pivot table using data from multiple spreadsheets in Excel 2000. I have the table created. However, I am only given the choices of "Row", "Column", and "Value" as my selection choices. Normally, in a pivot table, I can move a column such as Quarter to the left, then also select Line of Business, within that quarter, then get subtotals based on lines of business in their respective quarters. But now what happens in this consolidated range pivot, they list the Quarters, then the LOBs under the quarters. Way I need it to look: Q1 Northeast Southeast Subtotal Q2 Northeast Southeast Subtotal How it looks now: Q1 Q2 Subtotal Northeast Southeast Subtotal I have even tried to use the page function to put the Quarter at the top of the page, but it doesn't let me select a particular quarter, only gives me the field name. Does anyone know if it's possible using this consolidated feature? I know I can if I base the table on one sheet, rather than multiple sheets. But I can't seem to get it to work the way I need it to. Also, I cannot just combine all the sheets to one on a "data" tab since the information on each tab gets updated/added to each week. Thanks for any assistance you can provide! Rach -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table-Multiple Consolidation Ranges
Thanks. I will have to brush up on macros to try the combining after
weekly updates. Thanks for the tip! Rachelle Debra Dalgleish wrote: You can create a pivot table from multiple consolidation ranges, but the results may not be exactly what you want. There's more information he http://www.contextures.com/xlPivot08.html Perhaps you could create a macro to combine the data onto one sheet after the weekly updates. Rach wrote: Hello. I am trying to create a pivot table using data from multiple spreadsheets in Excel 2000. I have the table created. However, I am only given the choices of "Row", "Column", and "Value" as my selection choices. Normally, in a pivot table, I can move a column such as Quarter to the left, then also select Line of Business, within that quarter, then get subtotals based on lines of business in their respective quarters. But now what happens in this consolidated range pivot, they list the Quarters, then the LOBs under the quarters. Way I need it to look: Q1 Northeast Southeast Subtotal Q2 Northeast Southeast Subtotal How it looks now: Q1 Q2 Subtotal Northeast Southeast Subtotal I have even tried to use the page function to put the Quarter at the top of the page, but it doesn't let me select a particular quarter, only gives me the field name. Does anyone know if it's possible using this consolidated feature? I know I can if I base the table on one sheet, rather than multiple sheets. But I can't seem to get it to work the way I need it to. Also, I cannot just combine all the sheets to one on a "data" tab since the information on each tab gets updated/added to each week. Thanks for any assistance you can provide! Rach -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table-Multiple Consolidation Ranges
You're welcome, and if you have trouble when working on the macro, post
a question in the excel.programming newsgroup and someone may be able to help. Rach wrote: Thanks. I will have to brush up on macros to try the combining after weekly updates. Thanks for the tip! Rachelle Debra Dalgleish wrote: You can create a pivot table from multiple consolidation ranges, but the results may not be exactly what you want. There's more information he http://www.contextures.com/xlPivot08.html Perhaps you could create a macro to combine the data onto one sheet after the weekly updates. Rach wrote: Hello. I am trying to create a pivot table using data from multiple spreadsheets in Excel 2000. I have the table created. However, I am only given the choices of "Row", "Column", and "Value" as my selection choices. Normally, in a pivot table, I can move a column such as Quarter to the left, then also select Line of Business, within that quarter, then get subtotals based on lines of business in their respective quarters. But now what happens in this consolidated range pivot, they list the Quarters, then the LOBs under the quarters. Way I need it to look: Q1 Northeast Southeast Subtotal Q2 Northeast Southeast Subtotal How it looks now: Q1 Q2 Subtotal Northeast Southeast Subtotal I have even tried to use the page function to put the Quarter at the top of the page, but it doesn't let me select a particular quarter, only gives me the field name. Does anyone know if it's possible using this consolidated feature? I know I can if I base the table on one sheet, rather than multiple sheets. But I can't seem to get it to work the way I need it to. Also, I cannot just combine all the sheets to one on a "data" tab since the information on each tab gets updated/added to each week. Thanks for any assistance you can provide! Rach -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Consolidation Ranges in pivot tables | Excel Worksheet Functions | |||
| PivotTable - Multiple Consolidation Ranges | Excel Worksheet Functions | |||
| Pivot table page filter not accepting multiple values. Workaround? | Excel Worksheet Functions | |||
| how do I create a pivot table based on multiple sheets | Excel Discussion (Misc queries) | |||
| Pivot Table - consolidating ranges (again) | Excel Discussion (Misc queries) | |||
Hybrid Mode
