Link to other Excel documents

When making reports, multiple Excel documents are often used. As you know, data in another worksheet or other document, if it is open, we can easily access by specifying the absolute address of a cell or cell range. But what if we want to access a closed document? And can Excel be used as a data repository? About the link to other Excel files more in the text that follows.

If you remember, we form the absolute address of the cell by first giving the name of the document in square brackets, then the name of the worksheet, followed by the exclamation mark (!) and the address of the cell. When specifying the title of the document or a title of the worksheet, it happens that they consist of more than one word, so it is recommended that this part of the absolute cell address be listed under single quotation marks (‘). In other words, if we want to access A1 in the ‘Artikli’ document, which lies in ‘Artikli’ worksheet, and is located at the root of the C disk, we need to write the following formula:

=’C:\[Artikli.xlsx]Artikli’!A1

In case we make a mistake while defining the path to the Excel file with which we need to connect, a dialog box will open where we will be offered to search the disc to find that file. Confirming the entry, if we did everything correctly, the cell A1 content will appear. By copying to neighboring cells in Excel we will also retrieve data from the table ‘Artikli’.

If we want to view or update the link properties to another Excel file, you need to go to the Data ribbon, and then click on the Edit Links within the Connections group. The dialog box will open whose central part is a list where we can see existing links to external Excel files. Clicking the Update Values button will update the data in the new worksheet. That is, if we linked up with data from another worksheet, and in the meantime somebody changed them, they will be updated after launching this option. By clicking the Change Source button, we can change the source file from which the data is being retrieved. A dialog window will open where we can select a new source file. Clicking on the Open Source button opens the source file from which we have taken the data.

If we choose to click the Break Links button, the connection to the external file will be interrupted. That is, the data remains in the new worksheet, but they are converted into values. If we terminate the connection we will not be able to update the data in the future. By clicking Check Status, we will check the status of the connection, which will be shown in the list.

Beneath the list there are optional fields that allow you to define whether the data is being updated automatically or manually. Also, by clicking the Startup Prompt button it is possible to set whether Excel will warn us, after opening the document, to update the data in the list based on the data from the external file with which we have connected with.