#REF! error in linked worksheets

[Bryon Scruggs]

I have a cell value linked to another worksheet. This
uses the GETPIVOTDATA function to grab info from the
second worksheet. When I update the report I get a #REF!
error UNLESS I have the source data open as well. Any
ideas of how to fix this?

thanks,

Bryon

 

[AlanD]

Have you got "Update Remote References" and/or "Save External link values",
checked on your "Tools", "Options", "Calculations" tab?

Alan Day

 

[David]

I'm having the exact same problem as Bryon, and, yes, I do have these
options selected in Tools/Options/Calc tab.

I noted in a MSFT KB article that #REF! occurs if "the pivot data is
not visible" by design. For example, if you pivot on a field "REGION",
and you have say "North" selected, and your GETPIVOTDATA formula
references the Pivot Table with the value of "South" in the "REGION"
field, then you'd get the error. But it seems silly that the external
linked values aren't being retained.

Thanks in advance for any help!
David