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

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

Using the ABS Function

Need to find the absolute value of a number? That's where the ABS function comes into play.

Discover More

Selecting Tabs in Dialog Boxes

Dialog boxes normally present information in a series of tabs. If you want to move from tab to tab without taking your ...

Discover More

Protecting Tracked Changes

Track Changes is a great tool for editors and collaborators to use when creating documents. An author, seeking changes ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Saving Common Formulas

Got some formulas you slaved over and want to use in lots of workbooks? This tip presents some helpful ideas on how you ...

Discover More

Pulling Initial Letters from a String

When working with names or a different series of words, you may need to pull the initial letters from each word in the ...

Discover More

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
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 2 + 8?

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.