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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"baldmosher" wrote:

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

Thanks Shane, every day is a school day :-)