Error when Formula references a pivot table in an external workboo

  • Thread starter Thread starter baldmosher
  • Start date Start date
B

baldmosher

In my RESULTS2008.xls workbook, I'm using the following formula to pull in
data from a pivot table, which is held within another external workbook,
CREDITS2008.xls:

<Ignoring all the fluff... >
=GETPIVOTDATA("AMOUNT GBP",'C:\Documents and Settings\username\My
Documents\Reporting\datasources\[CREDITS2008.xls]Credit Note Detail'!$A$3,
<etc.> )

Is there a way of refreshing the data in RESULTS2008 without keeping
CREDITS2008 open in the background? If the file is not open when I update, I
get a #REF! error.

As you can imagine, this isn't the only external file that I'm referencing,
and a macro to open and close all my sources just to update the main report
seems a very silly way of doing things.

(If it matters, the main workbook I'm using is saved in the Reporting
folder, one above the datasources folder.)
 
Hi,

the GETPIVOTDATA is like INDIRECT, it doesn't support links to closed
workbooks. Use your GETPIVOTDATA funtion in the external file and then
reference that cell from your open file.
 
Back
Top