Excel, Getting Data From Another Workbook Through Links

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

One thought on “Excel, Getting Data From Another Workbook Through Links”

  1. Lorne Boyle says:

    Greetings,
    A good article that is related to my problem, which is: I get the “Automatic Update of external Links” message and am unable to actually find the cells with links. As you’ve noted, the linked spreadsheet is contained in braces indicating an external link as in [Source.xlsx]Sheet1!$A$2, however, searching for a “[” bears no fruit, as in not found anywhere in the worksheet.

    The spreadsheet is question is a monthly version …. January’s spreadsheet is saved as February which is then updated. In March, a new copy is made and so on. The phantom links are ‘old’ versions of the file.

    Are there any techniques you know to find the location of cells that have links; or, is there meta-data that might have embedded links in spreadsheets; or, are you aware of problems that MS Excel has with linking that causes these phantom links?

    Any/all responses greatly appreciated.

    Regards,
    LBoyle

    PS. I’m using Excel 2010

Leave a Reply

Your email address will not be published. Required fields are marked *