External Workbook References (Add-In)

Share this

March 13, 2013

 
External Workbook References (Add-In)
 
Problem

There are a lot of people that using external workbook references during their daily work. A sales manager for example could summarize the annual sales of a department by summing 12 monthly reports that are stored in 12 different workbooks. In this way he avoids copying all the data to a single workbook, making the annual report much more compact.

However there is a disadvantage of using external workbook references: if you move or delete one of the “independent” workbooks, then, when you reopen the “dependent” workbook, the following pop-up will appear:

 
External Workbook Reference Error
 
If you choose update, another pop-up will be shown:
 
External Workbook Reference Error 2
 
As you can easily imagine, if you have many external references in the “dependent” workbook it can be quite difficult to update them. Moreover, if the “dependent” workbook is used by many people, there is an increased possibility for a miscalculation, especially if an external reference fails to get the updated value.


Solution

The above limitation led me to write an Excel add-in that is capable to create a list of all the external workbook references of a workbook. In this way, the user can have a quick overview of all the references in the “dependent” workbook. Furthermore, the add-in can replace all the formulas that contain external references with their values. So, the user with a single click can clean the workbook from all the external references.

How to use it

The short video below demonstrates the add-in installation and use.

 
 

Tip

In general, I suggest you to avoid using external workbook references whenever it is possible. However, if this is impossible (for various reasons), then with this add-in you can have a clear overview of what happens in the “dependent” workbook.

 
 
 
 
Download it from here

 

 

This add-in can be used in Office 2007 or newer.

Page last modified: September 29, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>