#REF! & GETPIVOTDATA issue

jaime_mcd1373

New Member
Joined
Aug 2, 2006
Messages
4
I am populating cells in spreadsheet A using GETPIVOTDATA. The pivot table is saved in spreadsheet B, which is a huge file and takes a while to open. Problem is that if you don't have spreadsheet B open, A shows #REF! in the cell with the GETPIVOTDATA formula. Is there another way around this? These documents are shared by many people and I would prefer that they don't have to open B to get A to calculate correctly. Hope this makes sense. Thanks.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi Jaime

Simply not using GETPIVOTDATA is the way I get around this, however, this will only work if the pivot data structure doesn't change as you'll need references to worksheet cells eg:

Code:
='C:\Wherever\[Test.xls]Sheet1'!$A$1

Best regards

Richard
 
Upvote 0
Thanks, but I don't follow you. The pivot table is updated weekly, so I'm not sure how I could get by with not using GETPIVOTDATA.
 
Upvote 0
It may not work for you: it depends, like I said, if the pivot table structure changes ie new categories added, maybe new fields that need to be incorporated. If not, so that only the values within the pivot table changes (ie so one week we have Sum of Sales is $50m and the next week it is $60m but these values reside in exactly the same cell within the worksheet that holds the pivot table) then you simply don't need to use GETPIVOTDATA, you can use a hardcoded link as I suggested above. The hard-coded links will return a value even if the target workbook with the Pivot table is not open.

Does this mke sense?

Like I say, given your requirements, this may not be appropriate for you.

Richard
 
Upvote 0

Forum statistics

Threads
1,218,539
Messages
6,143,079
Members
450,461
Latest member
Bosavon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top