Pivot Table-Multiple Consolidation Ranges

[Rach]

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]

You can create a pivot table from multiple consolidation ranges, but the
results may not be exactly what you want. There's more information here:

http://www.contextures.com/xlPivot08.html

Perhaps you could create a macro to combine the data onto one sheet
after the weekly updates.

 

[Rach]

Thanks. I will have to brush up on macros to try the combining after
weekly updates. Thanks for the tip!

Rachelle

 

[Debra Dalgleish]

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.

Thanks. I will have to brush up on macros to try the combining after
weekly updates. Thanks for the tip!

Rachelle