#REF! & GETPIVOTDATA issue

[jaime_mcd1373]

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.

 

[Richard Schollar]

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:

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

Best regards

Richard

 

[jaime_mcd1373]

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]

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