The GETPIVOTDATA function, like some other functions, only returns data
for references in the same file, or another open file.

http://support.microsoft.com/default.aspx?id=299303

If you open the other workbooks, the formula results should work
correctly. Or use a different function, such as INDEX, to pull the data
from the closed workbooks.


Ang wrote:
Hi,

I am relatively new to pivot tables and thought i'd sussed it!, however, i
have 11 sales people each with very large sales forecast spreadsheets. To
extract the data i have created individual pivot table report worksheet in
each of their workbooks to extract the data required for each person. I then
have a main 'control centre' workbook (for the boss to view!) which pulls in
all of this extracted information (i.e. quotes/orders per sales person per
category per month) using =GetPivotData.

After i have been into each spreadsheet for user and updated and switched
back to my control centre workbook - all data appears fine. When i close
everything and reopen, i get #refs all the way through, however the formula
is appearing ok and the data is there to get.

When i was creating the spreadsheets i was advised to use the same data
source for each of the 5 pivots in each worksheet (as i kept selecting the
same range) to save memory which i accepted 'yes', i am wondering if this is
the problem but i do not know how to solve.

I am using XL2003.

If i open each person's spreadsheet and update* whilst control centre is
open the #refs disappear, if the user opens and updates, no response but the
macro is working ok.

*(i have created a macro which updates all pivot tables - did not know about
'refresh all' at this point!).

I hope this is enough info.

Any help would be appreciated, thanks,


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html