Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
How do I create a list in excel that contains external data? | Excel Discussion (Misc queries) | |||
Pivot tables, external data sources and ODBC links | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
I need to create pivot table for the month but data in days | Excel Discussion (Misc queries) |