Excel Smart Table as datasource for pivot

Question

I have a Workbook D:\Sales.xlsx with a sheet named Orders. In this sheet there is a smart Table (Ctrl + T) named tblOrders. In another Workbook E:\Reports.xlsx there is a sheet pvtOrders. When I create a pivot in this pvtOrders sheet and give D:\Sales.xlsx!tblOrders as datasource, it works fine while the source workbook Sales.xlsx is open in the same Excel instance. When the source is closed or opened in other Excel instance however refreshing of the pivot-Table is not working anymore. Excel prompts "Invalid reference" or similar (tested in Excel 2010, 2013).

The same problem is with a "normal" named range as source for the pivot table. However in this case there is no error prompt.

Is it a normal behavior? Is there a simple solution or at least a workaround without opening the source?

Thanks for any help

Answer 1

from my experience you need to have them open. have you considered merging both files? it might make things easier.