Tags

, , ,

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.


Step 1, open the source file:

Excel Links 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:

Excel Open File

Excel Links Output
Excel output 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:

Resize 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:

Excel 2 workbooks in one window


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:

Excel output file 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:

Excel Link


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:

Excel Link, Multiple Cells
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:

Excel Link, 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 Link, Multiple Cells


Excel Links, Security Warning:

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

Excel Links, Security Warning
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:


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:

Update Link
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:

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

Update Links


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:

 

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Advertisements