Excel 2019 Bible

Chapter 28: Linking and Consolidating Worksheets

28

If the workbook name or the sheet name in the reference includes one or more spaces, you

must enclose the text in single quotation marks. For example, here’s a formula that refers

to cell A1 on Sheet1 in a workbook named Annual Budget.xlsx:

='[Annual Budget.xlsx]Sheet1'!A1

When a formula links to a different workbook, you don’t need to open the other workbook.

However, if the workbook is closed and not in the current folder, you must add the com-

plete path to the reference. Here’s an example:

='C:\Data\Excel\Budget\[Annual Budget.xlsx]Sheet1'!A1

If the workbook is stored on the Internet, the formula will also include the URL. Here’s an

example:

='https://d.docs.live.net/86a6d7c1f41bd208/Documents/[Annual Budget

.xlsx]Sheet1'!A1

Single quotes are always required when the link includes a path or a URL, even if the path or URL includes no spaces.

Creating a link formula by pointing

Entering external reference formulas manually usually isn’t the best approach because you

can easily make an error. Instead, have Excel build the formula for you, as follows:

1. Open the source workbook.


2. Select the cell in the dependent workbook that will hold the formula.


3. Type an equal sign (=).


4. Activate the source workbook, select the source worksheet, and then select the
   cell or range and press Enter. The dependent workbook is reactivated.

When you point to the cell or range, Excel automatically takes care of the details and cre-

ates a syntactically correct external reference. When you’re using this method, the cell

reference is always an absolute reference (such as $A$1). If you plan to copy the formula

to create additional link formulas, you need to change the absolute reference to a relative

reference by removing the dollar signs for the cell address.

As long as the source workbook remains open, the external reference doesn’t include the

path (or URL) to the workbook. If you close the source workbook, however, the external

reference formulas change to include the full path (or URL).

Externally linked cells can also be an argument to a function, like SUM or VLOOKUP.

Anywhere you would point to a cell on the same workbook as the function, you can point

to a cell in another workbook. A SUM function with an external link looks like this:

=SUM([source.xlsx]Sheet1!$B$3:$B$5)