#Value error from external links only if using diff version of Excel

[Jen Kuntz]

I've got a client having issues with Excel and sharing workbooks with links in them.

The short version is the file is an .xls file created in Excel 2013, with SUMIF formulas linking to another workbook. The formulas are simple SUMIF, and far less than 255 character limits implied as an issue on these older .xls version workbooks.

The creator is sharing this workbook out to others with the links still there instead of "paste values", even though the end user doesn't have access to the source workbook because it's always worked fine, users have always been able to see the data as long as they don't try to refresh the workbook. All users are on the same network.

• If the users are on the same version as the creator, they can open the workbook just fine and it displays whatever data was there when the creator saved it last.
• Users in Excel 2016 get "#value!" errors instead, so they can't use the workbook or view the data. Those same users *can* see the data via the preview function in Outlook (this particular report is emailed to users).

Nothing I enable in Excel 2016 works. The closest I can get is "seeing" the data briefly before it returns to #VALUE! errors. If I go to Options > Advanced > General and enable "Ask to update automatic links", when I open the workbook, I am prompted with a warning and a choice of Update or Don't Update. While that dialog box is on the screen, I see the dollar values in the cells as I expect to see. No matter which option I select, both of them change the values to "#VALUE!". I would expect that "Don't Update" leaves the data intact but it doesn't.

This is only an issue when users are not using the same version of Excel but I cannot find any settings that differ between the Excel 2013 and 2016 options that might explain this behavior. Ultimately I need to find a better way for the users to share the data without the formulas but since this has not been an issue in the past, they are anxious to try to see why Excel acts differently now than it did before.

[#2]

I'm afraid not much can be done about this. If Excel detects that a workbook has last been opened in an older version of Excel it will always recalculate the workbook. This has been the case for various versions of Excel so no difference in behaviour, other than that your users have to deal with more than one version of Excel in your organisation.

[#3]

Thanks Jan.
Is the same true if someone opens the workbook in an older version of Excel? I.E. created and saved in a newer version, opened in a prior version? They tell me it only happens when the version isn't the same, and not necessarily when it's newer, sometimes a user had an older version. (as they roll out Office 2016, they find this happens to 2013 users).

[#4 · Accepted solution]

I just tried with Excel 2010 and 2016 (haven't got 2013):
- If saved in 2010 and opened in 2016 a full recalc is done
- If saved in 2016 and opened in 2010 no calc is done.

[#5]

Thanks, that's helpful. It seems this client has it going both ways but I need to test that to validate if my results match yours.