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

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

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

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