Links to other workbooks

  • Apr. 4, 2021

Introduction

Believe it or not after my about 25 years of Excel usage... Imagine this case. Cells in an Excel workbook retrieve information from other cells. But these source cells not only are part of a different sheet, no, these worksheets are part of a different workbook. And this source workbook does not need to be open in Excel but rather closed. Much like the illustration below, where we add up sales totals for Belgium, France and Germany in a different workbook. Each time we have 1 source workbook per country:

This is working fine. However, problems arise when the files are transferred to a different folder / drive / PC / a coworker's PC/... The D-drive might not be D anymore. The folder called "Excel" might not exist anymore or needs to be changed. The coworker changed the name of a workbook (by accident or on purpose). Or the name of the sheet in the source files changed - maybe only in 1 source file or in all. You guess so: trouble !

You will see the formula for column B in column C: it makes a difference whether the source workbook is opened or not. When opened, the cell reference is shorter compared to when it would be closed, because then Excel shows the full path towards the file. You don't need to do this all by yourself: enter an = sign, open up the source workbook, point to the source cell with the mouse and close the source workbook. Excel will add the full syntax.

When I was confronted with such a situation where I needed to change (a bunch of) cell links, I typically used "Find and Replace" for the paths in the formulas. But Excel offers a lot of support here. I knew this in the back of my mind but I never used it actively. Until now !

Excel links and references

In the Data menu Excel has an option to look at the different links. You can change them accordingly:

This way you can correct links again without doing a number of Find/Replace actions in Excel.






Next time when you wonder what other workbooks are referenced in the Excel formulas, go to this menu option. It will add clarity and allow you to change, update or break links.

For those of you who want to automate with VBA, here is code: 

Sub Change_Excel_References()

    ActiveWorkbook.ChangeLink _
        Name:="D:\Excel\Sources\Belgium.xlsx", _
        NewName:="D:\Excel\Sales totals\Sources\Belgium.xlsx", _
        Type:=xlExcelLinks


End Sub



Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links