Getting Pivot talbe data (Excel 2003)

Topic

New Reply

I am creating a dashboard-type excel file for management. I want to show them a Pivot table or two based on data in another Excel file. I am trying to avoid placing the whole datafile in the report workbook by getting the PT source to reach into the source data workbook from the dashboard workbook. When, in the dashboard workbook, I start the PTWizard and select the Microsoft Excel file source and try to browse to it, the Range box shows the file name.xls! and says Reference is not Valid when I click Next. I have a tab DATA with a dynamic range name set up. First, can i do this? Second, I tried (a little) that External data source route with no success. Is that the way to do it? TYIA

Reply | Quote

Replies

Hi,
When you click on the browse button, it adds the path to the source workbook in the textbox…you still need to type the range reference pointing to the cells that contain the list. Once you type the lists cell reference, you can choose next!
Cheers

Reply | Quote

Set the source to ‘Other workbook.xls’!RangeName where Other workbook.xls is the name of the source workbook (which must be open) and RangeName is the name of the dynamic range.

Reply | Quote

Ok. It seems that the source workbook must be open. I was hoping that would not be necessary. Is this correct? Are there alternatives? I think sumproduct can reach into a closed workbook but PTs cannot?

Reply | Quote

Since a pivot table is not updated automatically, you can close the source workbook after creating the pivot table.
If the source range is fixed, you can even update the pivot table when the source workbook is closed.
But if the source range is dynamic, the source workbook must be open when you update the pivot table – Excel cannot read a dynamic range in a closed workbook.

Reply | Quote

OK Thanks for the clarification

Reply | Quote

If you prefer not to have the data source open, can you include a “before close” routine that will rename the required named range to the same range that would have been calculated for the dynamic range? That way the dashboard file should be able to read the data for the Pivot Table even if the data file is closed.

Reply | Quote