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,

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

Thank you, have reallocated my pivot tables!

"Debra Dalgleish" wrote:

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