#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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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,176,124
Messages
5,901,524
Members
434,899
Latest member
powerappsjoker99

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