#Ref! error when trying to link to external Pivot Tables in Excel 2007

Hi,

I am having issues with my Excel 2007.

Basically I am trying to link to Pivot Tables in external spreadsheets. Whenever i try this i get a #REF! error.

I have found that the only way to get around this is by clicking the 'Data' Tab -> Click 'Edit Links' button -> Highlighting all the links and Clicking 'Open Source'.

This however is not really much help as i could be linking to 20 - 30 external spreadsheets and cannot open every single one at the same time.

Does anyone know of a way to avoid the #REF error without having to open all the external spreadsheets it references????

Or is opening up all the external spreadsheets the only way??

any help with this would be very much appreciated!

thanks,

Peter

 

 

Question Info


Last updated February 6, 2019 Views 2,252 Applies to:
Answer
Answer

As you have found out, you can't generally refer to data in a closed workbook.

There are a couple of VBA methods out there to let you get data from a closed workbook, but you will find that they are slow, especially if you have a lot of links.

See Laurent Longre's free add-in at http://xcell05.free.fr/morefunc/english/

Or look at Harlan Grove's take on this at http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e249f6c074a3adfd


Ed Ferrero www.edferrero.com
Ed Ferrero www.edferrero.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.