Links between Two Desktop Workbooks

Ulises has two workbooks, both on his desktop. When he opens the first workbook, he receives #VALUE errors for all links to the second workbook. The errors disappear when the second workbook is opened. Ulises wonders why Excel can't derive the linked values from the second workbook automatically and why the second file needs to be open.

The easy answer is to say that this occurs because Excel doesn't know that the other workbook exists until it is open. If you take this approach, then it would be a relatively easy task to create a macro in each workbook that tests whether the other workbook is open and, if it isn't, opens that workbook. This would ensure that opening one workbook would open both, automatically.

Note I mentioned that this was "the easy answer." That's because Excel doesn't treat all of its functions the same when it comes to cross-workbook references. Consider, for example, the following two formulas:

=SUMIFS('D:\MyFiles\[Book2.xlsx]Sheet1'!$A$1:$A$5, 'D:\MyFiles\[Book2.xlsx]Sheet1'!$B$1:$B$5,"TRUE")
=SUMPRODUCT(''D:\MyFiles\[Book2.xlsx]Sheet1'!$A$1:$A$5*('D:\MyFiles\[Book2.xlsx]Sheet1'!$B$1:$B$5=TRUE))

If Book2.xlsx isn't open, then the first formula will generate an error, but the second formula won't—the second one will grab the desired value from the closed workbook. The functions used in the formulas behave differently when it comes to referencing a closed workbook.

This isn't the only example, however. It seems that many of the older functions (such as SUM, SUMPRODUCT, VLOOKUP, INDEX, and COUNT) all work fine when the external workbook is closed, but newer ones (such as SUMIF, SUMIFS, COUNTIF, and COUNTIFS) don't work. This isn't a hard-and-fast case of "older vs. newer functions," however, because INDIRECT (older function) doesn't work and INDIRECT.EXT (newer function) does work. There is little rhyme and reason to the inconsistency.

Instead, the problem seems to be related to how the functions are coded internally within Excel. The mere fact that not all of the functions behave consistently when dealing with external references indicates that they were coded at different times using different standards and different approaches. It also means that the only way you'll know which functions will work with external references to closed workbooks is to try it out. If a function doesn't work in that situation, then you can try a different function to see if it will.

There is one other thing to keep in mind—single-cell references to closed external workbooks seem to work just fine. Thus, if you are referencing a limited number of cells in the second workbook, you could use simple, single-cell references to those cells, such as the following:

='D:\MyFiles\[Book2.xlsx]Sheet1'!$A$1

This should come across just fine, even if Book2.xlsx is closed. And, if this reference is in a cell in your worksheet such as F3, then you can reference F3 within other worksheet formulas and have no problem whatsoever.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12457) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.