create one pivot from mulitple data sources

[news.hp.com]

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]

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 here:

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

[Ed Ferrero]

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

[Md. Nazrul Islam]

i am in now excel forum

[Dineth]

you can define relationship and then take two or more tables to one pivot table.