Dec 11 2017 12:35 PM
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.
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.
Dec 12 2017 06:15 AM
Dec 12 2017 06:42 AM
Dec 12 2017 06:58 AM
SolutionDec 12 2017 07:51 AM
Dec 12 2017 06:58 AM
Solution