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