Excel 2013 - External Links not updating unless source file open

our office is slowly migrating from excel 2010 across to 2013 and we are seeing a specific issue with 2013 and files that contain external links.

Example: Worbook A was created in Excel 2010 and contains external links to Workbook Z. Users on 2010 version, can open Workbook A and see data results from links. The user may or maynot have permission to access source file Workbook Z but this isn't usually a problem to just view last saved Workbook A.
What changes is if a 2013 user opens Workbook A... even if they have persmission to access Workbook Z, when opening Workbook A, all they see is #Ref in each cell that is related to linked data. If the user refers to Data>Edit Links, they can 'update values' and this will return 'OK' in the status... yet it still will only show #Refs. The link references are largely to pivot tables (created with 2010) but we are also seeing similar issues with 'sumif' formula links.

The only way to retrieve data is either for Workbook A to be saved without links, or for the 2013 user to open the source Workbook Z file. Due to the way of working/file sharing in our office, this is an untenable way of working.

We have reviewed all the trust settings in Excel (both versions), checked linked naming conventions and ensured they're refering to server absolute addresses, rather than individual computer named pathways and have done a lot of 'Googling'. There are some web references to issues with Pivot Table links not updating in relation to 2013 upgrade and the only answers seem to be 'save without links' or 'open source file'.  That isn't a practicle solution... users must be able to view without having to open source file and simply editing links out is not a workable option.  2010 version works very well, so is there a setting within 2013 that we are missing? 

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Does this also happen to sets of files that are all created in and saved from Excel 2013?
Regards,
Jan Karel Pieterse
Excel MVP
https://jkp-ads.com
https://excelexperts.nl

4 people were helped by this reply

·

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.

A slightly different result...

On the basis of a file being created on version 2013 and being opened by another 2013 user:

The user can open the destination file and if they do not 'update links' on the prompt, then numbers are displayed.  However, if 'update links' is selected, then this results in a #Ref result.  #Ref remains, even if the user selects Data>Update Links and the status shows 'ok' for source file.  The user has permissions to open source file, which is located in the exact same drive/folder.  As before, if the user then opens the source file, then destination file can be edited and saved without loss of data visibility.

1 person was helped by this reply

·

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.

Some functions do not allow recalculation without the source file being open in Excel, other do allow for that. When an Excel file is opened by Excel 2013, it first checks in which Excel version it was last recalculated. If it is not 2013, it will recalc, yielding the #REF errors for the cells pointing to or containing functions which need the source open.

You may have to redesign the workbook., perhaps look for alternative functions to achieve the same result that do allow the source wb to be closed.

Regards,
Jan Karel Pieterse
Excel MVP
https://jkp-ads.com
https://excelexperts.nl

4 people were helped by this reply

·

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.

Hi,

SUMIF() function will not work when the source Excel workbook is closed and this statement is TRUE for all versions of MS Excel.  If you can post the SUMIF() function here, then I can suggest an alternative SUMPRODUCT() formula which will work even when the source workbook is closed.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

223 people were helped by this reply

·

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.

Thank you Jan.  Its dissapointing that in some cases the source file now needs to be open.  This poses an issue for us, as some users do not have permissions to see full source file.  It also feels like a step back, in relation to userability but your explanation has been helpful and gives me something to work with.

Thank you Ashish, I am familiar with the SUMIF / SUMPRODUCT formula, so will deal with that.

many thanks for your replies.

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.

One possible way to work around this issue is by pulling in the data needed from the other workbook using an external data query. That way you can refresh the connection when the linked workbook is available and have the most up-to-date information. When the linked workbook is unavailable, the data will remain in the report and formulas pointing to that data will still work.
Regards,
Jan Karel Pieterse
Excel MVP
https://jkp-ads.com
https://excelexperts.nl

1 person was helped by this reply

·

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.

Hi Ashish,

this is exactly the problem I have just discovered. I cannot update the destination file without have all the source files open. definitely an issue I would love to fix. my cells are just showing #value

I am using Office 2010

These are the 2 formulas I am using.

=SUMIFS(‘[SourceFile.xlsm]JUL-15'!$G$3:$G$62,[SourceFile.xlsm]JUL-15'!$D$3:$D$62,' TOTALS'!$A$1,’SourceFile.xlsm]JUL-15'!$E$3:$E$62,'TOTALS'!$A3)

=COUNTIFS('[SourceFile.xlsm]JUL-15'!$D$3:$D$62,'TOTALS'!$A$1,'[SourceFile.xlsm]JUL-15'!$E$3:$E$62,’ TOTALS'!$A3)

My source file requires staff to select the product, then in the next column place the value of the product.

my SUMIFS formula adds up the values if it equals the product next to the destination cell.

The COUNTIFS adds up the number of products sold.

Any help would be greatly appreciated.

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.

Hi,

Try this in place of the SUMIFS

=SUMPRODUCT(([SourceFile.xlsm]JUL-15'!$D$3:$D$62='TOTALS'!$A$1)*(’SourceFile.xlsm]JUL-15'!$E$3:$E$62='TOTALS'!$A3)*([SourceFile.xlsm]JUL-15'!$G$3:$G$62))

Try this in place of the COUNTIFS

SUMPRODUCT(([SourceFile.xlsm]JUL-15'!$D$3:$D$62='TOTALS'!$A$1)*(’SourceFile.xlsm]JUL-15'!$E$3:$E$62='TOTALS'!$A3))

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

12 people were helped by this reply

·

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.

We are having the same issue with Excel 2013 showing #VALUE! for external data. But 2010 shows the value.

What was the solution to your issue? The external data link is not accessible by 2010 client or 2013 client.

I also broke all the links in 2010 and my 2013 still could not see the values.

Thanks for the advice or fix if you have it.

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.

I found a common theme is where a spreadsheet/file had been created in earlier versions and upgraded to 2010, these worked fine.  Files that were created in 2010 or 2013 do not work. The only solutions were as suggested in earlier threads... using SUMIF.  However, if wanting a dynamic reference to a pivot table, this doesn't work.  I am at a loss with this 'upgrade'.  I have multiple files that work with pivots from other workbooks... it is a real problem and I have had no useful explanation from MS as to why such a simple feature cannot exist.

4 people were helped by this reply

·

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.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated July 7, 2020 Views 77,874 Applies to: