#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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

jaime_mcd1373

New Member
Joined
Aug 2, 2006
Messages
4
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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,141,756
Messages
5,708,329
Members
421,565
Latest member
Lastadiego

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
Top