Pivottable getpivotdata problem

[Mikmac]

have a pivottable in one workbook. In another one i use GETPIVOTDATA and use
update to get the data. The problem is that it only works when i have the
workbook with the pivottable open else there is zero i all cells? Why?

Is it possible also automatically update the data without using update?


Please help me!!!--
Kind regards Michael

 

[Tom Hutchins]

It appears that GETPIVOTDATA only works when the workbook with the pivottable
is open. A number of other functions, such as SUMIF, require the source
workbook to be open.

Here is a workaround: Put the GETPIVOTDATA formulas in another sheet in
workbook with the pivottable. Then link to those cells from the other
workbook. I prefer to name each cell I am going to link to from another
workbook; this ensures that the links work if the linked cells are moved in
the source workbook, and the range names help to identify what each link is
for.

Hope this helps,

Hutch

 

[Tom Hutchins]

Regarding the last question in your post: you can avoid the Update dialog
when you open your workbook, and have the links updated automatically at that
time, by selecting Tools >> Options >> Edit tab >> uncheck the "Ask to update
automatic links" checkbox >> OK. These directions are based on Excel 2003. I
don't have 2007 available at the moment, but the setting is probably found
under Office button >> Excel options.

Hope this helps,

Hutch