Links between Two Desktop Workbooks

Written by Allen Wyatt (last updated October 29, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021

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")

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:


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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Highlighting the Rows of Selected Cells

If you lose your place on the screen quite often, you might find it helpful to have not just a single cell highlighted, ...

Discover More

Setting the Calculation Default

Excel can recalculate your worksheets either automatically or manually. The default is to calculate them automatically, ...

Discover More

Securing Your Signature

If you want to "sign" your documents, you might be tempted to insert a graphic scan of your signature into them. Before ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Activating the Formula Bar with the Keyboard

Hate to take your hands off the keyboard while working on a worksheet? Here's one way to activate the Formula Bar without ...

Discover More

Grabbing the Second-to-Last Value in a Column

Need to get at the next-to-last value in a column, regardless of how many cells are used within that column? This tip ...

Discover More

Counting Asterisks in a Column

Excel can be used as a simple database program. If you use asterisks in a column of your database to designate ranking of ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 5?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.