PIVOT TABLES: Multiple Data Ranges

  • Thread starter Thread starter Joe Hewitt
  • Start date Start date
J

Joe Hewitt

All,

I'd like to create a pivot table from three (3) separate
data files - all of the exact same format with the same
header row. I'd really like to get the data field
options that I get with a single range, but I can't seem
to make this happen.

I know I'm missing something here, but I don't know where
to turn.

Any pointers?

Please e-mail a copy of your response to me at
(e-mail address removed).

Thanks in advance...

--Joe
 
Dear Joe
Using the pivot table wizard (Data/Pivot table and Pivot
Chart Report). When the wizard fires up, select
the "Multiple consolidation Ranges" option, and then
follow the wizard from there.

HTH

Paul
 
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.

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. Or, use
MS Query to import the data (Data>Import External Data> New Database
Query) and use an SQL UNION statement to combine the data into one table.
 
Back
Top