In Excel 2016 when you open several files that have cells linked, in mots cases you get reff error, however excel 2013 and below has no such issues. How can this be corrected?
In Excel 2016 when you open several files that have cells linked, in mots cases you get reff error, however excel 2013 and below has no such issues. How can this be corrected?
A REF# error occurs when the referenced data source can't be identified. This might be caused by a missing (renamed) range or worksheet or, if you reference data in another workbook by a missing (renamed) tab or range in that other workbook.
Based on what you say, I suspect that in your case the workbook itself can't be identified. A workbook is identified by its FullFileName which is concatenated from the workbook's own name and the Path that defines the folder where it is kept. Check your formulas. If the file name doesn't include the Path you alredy found the reason as well as the way to cure the problem.
When you create a formula referencing another open workbook Excel will include the FileName automatically but not the FullFileName. This is because the FileName is sufficient to identify an open workbook - better to say, another workbook open in the same instance of Excel. If you now save the workbook and close it the formulas will throw a REF# error next time you open the workbook unless the referenced workbook is again also open. This is because the Path is missing.
There are several ways of replacing a FileName in formulas with the corresponding FullFileName. There is a pretty good description of the steps at this link.