create one pivot from mulitple data sources
 

Can anyone help point me to references regarding building a single pivot
table from multiple data sources.

For example, If I have two different worksheets that have a common data
field that can be used to correlate the two sets of data, can a pivot table
be built that uses both sets of data?

What about if one set of data is in a database and the other set of data is
in an excel worksheet?

I don't have control of the external database, so if I need to pull the data
together 1st, before making a pivot, then I would have to create another
database of my own and import both sets of data. Is that the recommended
solution?

You can create a Pivot Table from multiple consolidation ranges, but you
won't get the same pivot table layout that you'd get from a single
range. There's an example he

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

If you can combine the data in your own database, then use that as the
source, you'll have more flexibility in creating the pivot table.


news.hp.com wrote:
Can anyone help point me to references regarding building a single pivot
table from multiple data sources.

For example, If I have two different worksheets that have a common data
field that can be used to correlate the two sets of data, can a pivot table
be built that uses both sets of data?

What about if one set of data is in a database and the other set of data is
in an excel worksheet?

I don't have control of the external database, so if I need to pull the data
together 1st, before making a pivot, then I would have to create another
database of my own and import both sets of data. Is that the recommended
solution?




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi,

Can anyone help point me to references regarding building a single pivot
table from multiple data sources.

You cant. You first need to consolidate the data into one source.

For example, If I have two different worksheets that have a common data
field that can be used to correlate the two sets of data, can a pivot
table
be built that uses both sets of data?

First consolidate the data into one worksheet. Use VLOOKUP formulas to link
the two data sets based on the common field. See
http://edferrero.m6.net/DataTutor1.html for information on how to do this.

What about if one set of data is in a database and the other set of data
is
in an excel worksheet?

I don't have control of the external database, so if I need to pull the
data
together 1st, before making a pivot, then I would have to create another
database of my own and import both sets of data. Is that the
recommended
solution?

Yes. In that case I would recommend creating an Access database and linking
tables from the external db and from the Excel worksheet. Build a query to
consolidate the data and use the query as the data source for the pivot
table/

If you are not comfortable working in Access, you can always use Excel to
comsolidate the data. Use Excel's Database Query capability to import the
database data, then use VLOOKUP to combine that with the other worksheet
data.

Ed Ferrero