Excel, Getting Data From Another Workbook Through Links

Jul 03, 2015 by azurous in Excel

In this article I will explain how you can get data from another workbook using links. One of the good parts about using links is that you don’t need to actually open the file.

–

Contents

Step 1, open the source file:

–

Step 2, Open the Output File Using the Open File Dialog:

In this step you must open the output file using the open file dialog from the source file:

Note: You probably will not be able to see both files at the same time. By pressing the resize window button in the figure below you will be able to see both files in same window:

Result:

As you can see in the figure below on the right side I have my output workbook and on the left side I have my source workbook:

–

Step 3:

Click on a cell in the output file where you would like the value from the source file to appear. In this case we have selected the cell A2:

–

Step 4:

Type the “=” character then click on a cell in the source workbook where you would like the value to come from:

–

Applying Links to Multiple Cells:

In the previous example had we copied the formula to the rest of the cells in the output workbook, we would have ended up with this:

As you can see the same value in cell A2 of the source was copied throughout all the cells in the output workbook. The reason for this is because the default formula when linking to a cell in another workbook has the “$” sign for columns and rows. Lets take a closer look at the formula in cell A2:

The formula in cell A2:

=[Source.xlsx]Sheet1!$A$2

This can be overcome by removing the “$” signs first, then copying the formula to the rest of the cells:

–

Excel Links, Security Warning:

After creating links and reopening the file you might see something like this:

The links in the workbook will not be updated until the links are given permission. This can be done by clicking the “Enable Content” button. For more information on this topic please see the link below:

Security Warning: Automatic Update of Links Has Been Disabled

Changes in Source File:

In order for changes in the source file to appear in the output file the following steps must be taken:

Step 1:

Save the source file. Changes in the source file will not appear in the output file until the file is saved.

Step 2:

Update links. This can be done in several ways.

Method 1: When you first open the file, click on the Update button in the dialog that opens:

Method 2: The links could also be updated by clicking on the edit link button on the Data ribbon. On the window that opens click on Update Values:

Method 3: Click on the Calculate Now or Update Sheet buttons on the Formulas ribbon:

–

Large Amount of Links and Slow Workbooks:

When you have large number of external links in a workbook, the workbook will tend to perform with a lag. Although the process of updating links is manual, but each of these external links is itself a formula, therefore every time you make changes to a cell, Excel will try to re evaluate all these formulas. This can be overcome by changing automatic updates of formulas to manual. I have covered this topic in detail in the article below:

Excel Change Formula Calculation to Manual

VBA and VB.Net Tutorials, Education and Programming Services