Error when Formula references a pivot table in an external workboo

[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.)

 

[Shane Devenshire]

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.

 

[baldmosher]

Thanks Shane, every day is a school day