Error when Formula references a pivot table in an external workboo

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Expand the fields used in a Datasource for a Pivot Table 0
XML error in Pivot Table 0
Pivot Table Refresh Error 0
I am trying to refer to a cell to open an external link 8
Workbook with external links shows REF! error in 2007 not in 2003 0
Link a file to a Pivot Table in another file 0
Can a pivot table be redirected to an external source? 1
variable substitution in a formula linking to external workbook 2

Top