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

Printing Shortcut Key Assignments from a Macro

Need to know what shortcut keys are defined? You can use a single macro command line to print out the definitions.

Discover More

Automatic Sound Clips

Want to add some sound to your documents? It's easy. What isn't so easy is configuring a sound clip to play whenever a ...

Discover More

Cleaning Up a Document that Mixes Styles with Direct Formatting

Need to get rid of direct, explicit formatting applied to a document? Here's an easy way to do it using familiar Word tools.

Discover More

Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!

More ExcelTips (ribbon)

Finding the Smallest Even Value

When processing data in a worksheet, you may have a need to know what the smallest (lowest) even value in a range is. You ...

Discover More

Cell and Name References in COUNTIF

The second parameter of the COUNTIF function is used to specify the criteria to be used when determining what should be ...

Discover More

Inserting the Workbook Name

Do you want to insert the name of your Excel workbook into a cell? There are a number of ways you can do this, as ...

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 7 + 1?

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.