Pivot table from multiple consolidation ranges - Bug?

[Louise]

I have an Excel file with a list of deals transacted that
contains data with say ten column labels. On each row,
only one column; commission income, is meant to be
summed, and the rest are client name, broker, instrument
type, date of transaction etc that are meant for
filtering or specifying the commission in any way I
choose. I use a pivot table to make a commission report
that shows for instance total commission per client in
January through March, specified with subtotals for
instrument type and broker etc. So far everything has
worked well, but the file with the data has grown so
large that I now need two worksheets. But trying to use
the "Use multiple consolidation ranges" in the Pivot
table report wizard, I get a pivot table that either sums
or counts each field. Is this a known bug, or am I doing
something wrong? (I might add that I tried the tutorial
example on the Microsoft Excel support page, so I don't
think I'm doing anything wrong. But their example only
contains one type of data on each row, such as income.)

I know I could work around this problem by filtering only
January through March transactions, copying and fitting
all rows that I need at the moment in one worksheet. But
that's exactly what I am trying to avoid since the next
time I may want to see only European clients in January,
so I would have to filter again.

Any way to do this without using an external database?

Help is very much appreciated!

//Louise

 

[Debra Dalgleish]

You can create a PivotTable from multiple consolidation ranges, but you
won't get the same pivot table layout that you would from a single range.

If Client Name is the first column in your data source, the row heading
should show the names. The column area will show each of the remaining
headings. You can change the function that's being used by the data
value, but it will use the same function on all these columns.

The PivotTable would contain some meaningless data, such as Sum of
Order# or columns full of zeros for database columns that contain text.
To avoid this, you can rearrange your database columns, and then use
data ranges that only include the columns that you want to total. Or,
after you've created the Pivot Table, hide the column and row items that
you don't need.

If possible, store the data in a database, such as Access, and use that
as the source for the PivotTable.