Links between Two Desktop Workbooks

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


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, 2021, and Excel in Microsoft 365.

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. ...

MORE FROM ALLEN

Conditional Printing

If you need to make what Excel prints be based upon conditions in a worksheet, you'll love the information in this tip. ...

Discover More

Hyperlinks to Charts

You can create hyperlinks to all sorts of worksheets in a workbook, but you cannot create a hyperlink to a chart sheet. ...

Discover More

Formatting Captions

When you add captions to elements in your document, Word allows you to modify how those captions are formatted. Here's ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!

More ExcelTips (ribbon)

Indirectly Referencing a Cell on a Different Worksheet

Excel includes the powerful INDIRECT function which can be used to assemble references to other cells in your workbook. ...

Discover More

Shortcut for Viewing Formulas

If you need to switch between viewing formulas and viewing the results of those formulas, you'll love the keyboard ...

Discover More

Generating Random Strings

Do you need to generate strings of random characters? The ideas presented in this tip will help you do it in a hurry.

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 5 + 3?

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
Subscribe

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.