Hello,

I want to make a central database that I can use as the source for several different reports. I currently have about 10,000 rows of data (although it grows pretty slowly).

I'd really like to keep all the raw data in one workbook and build each report in a separate workbook, using pivot tables which all reference the document with the data.

My questions are:

1. Can I build pivot tables whose source data is in a separate workbook? If so, then how?
2. Is excel the best tool for this? If not, what suggestions do you have.

Previously, I've been keeping the data and all the reports in a single workbook, but I soon had a very large number of worksheets and it became difficult to find things. I add the data manually because the program which generates it can export it to excel, making the copy/paste process very easy.

Much appreciated,

bryanbr4un

Excel does work pretty well for this. To make it easier set up your target Workbook, the book having the Pivot tables with an External Database Query which points to your XL Data file. Use the Menu "Data->Import External Data->New Database Query... " to open Microsoft Query and configure a connection to your data source. Have the data returned to your Pivot Table workbook. You can set options to have the data refresh on workbook open or you can manually refresh the data. Once the data query is setup configure your Pivot tables using this data.

You can set up the External data directly to the Pivot table also. On the Pivot table wizard select External Data Source.

Eeither of these methods will require the Microsoft Query program which is included with the MS Office Professional edition. It is an optional feature that may not be installed by default. It should install on first use or you may need your MS Office installatoin disk to install it.

I appreciate the help and the advice. I've been working on it and I got it set up really nicely. Thanks again.