Pivot table from Multiple Consolidation Ranges

  • Thread starter Thread starter Eddy Sjauw
  • Start date Start date
E

Eddy Sjauw

Dear All Excel Experts,

Creating a Pivot Table from 1 data table in 1 worksheet
gives me the Pivot Table the way I want it, meaning as
many Page Fields as I defined them at the top left of the
Pivot Table. These Page Fields represent the column lables
and in the drop-down I can choose the data under that
lable of the respective column.

Now I wish to have precise the same Pivot Table, but this
time from data tables spread over several worksheets,
whereby the format of these data tables are exactly the
same. But now the drop-down of the Page Fields at the top
left of the Pivot Table give the choice to choose the
worksheets in stead of the column lables with the data of
these columns.

My question is how do I create a Pivot Table from several
worksheets as if the data is from one worksheet only. Copy
and paste the data tables from the several worksheet into
1 worksheet is much to troublesome.

Thanks a lot for your help.

Regards,
Eddy
 
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 Date is the first column in your data source, the row heading should
show the dates. 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, if possible, you could store the data in one table in a database,
such as Access, and use that as the source for the PivotTable.
 
Depending on the nature of the data and how exactly you are trying to
consolidate the data depends on the best solution.

I had to consolidate data from two different sources but the layout was
exactly the same, it was just not possible to export this information
to my database at the same time.

The simplest solution if you can use i.e. Access database is to create
a table and export/paste the data from the various sources, then create
the pivot table telling the wizard it is coming from an Acces .mdb
file.

I am curious to know what the difference is between the two different
data sources is it where you sourced the information or is it something
like different dates/years/months ?

With a little more info I might be able to point you in the right
direction.

GarethG
 
Dear Debra & GarethG,

The way Debra has suggested is already tested by me and it works. Pity
that it takes more steps, but I am helped. Thanks so much.

Regards,
Eddy
 
Back
Top