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?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
REF ERRORS
0
Discussion
Did you create this file originally in .xls format and upgrade it at some point to .xlsx? If so, try saving the files back in .xls by using SaveAs and changing the file type - maybe that can help? If that doesn't help, edit your question and input a sample formula that links the two workbooks and provide any additional info. that you can to help troubleshoot it.
Also, make sure to have backups of the original files in case troubleshooting the errors causes more issues.
Also, make sure to have backups of the original files in case troubleshooting the errors causes more issues.
don (rep: 1989) Jul 8, '20 at 1:50 pm
Answers
0
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.