Microsoft Excel —

Mega-Formulas that Reference Other Workbooks

Excel formulas get pretty complicated when a mega-formula references another workbook. Not only do you need to include cell references, but also you must include workbook names or sheet names, and even the full path if the referenced workbook is closed. There are several ways to simplify what can be a complex process.

Writing such formulas from scratch can become unwieldy quickly. In this tutorial, we will show you a quick and easy way that enables you to construct these formulas without the need for workbook names and file paths. The method is so simple it is often overlooked.

Let's first ensure that you use the correct means to reference cells and worksheets. When writing a formula, it is always a good idea to never type cell references, sheet names, or workbook names because this can introduce incorrect syntax and/or typos. Most people at an intermediate level should be using only their mouse pointer to reference cells, sheets, and workbooks. This certainly goes a long way toward preventing syntax errors and typos, but if you have ever done this with a nested function, you know the formula quickly becomes unwieldy and is very difficult to follow.

For instance, take a look at this formula:

=INT(SUM('C:\Finance\SoftwareSales\[Regnow.xls]Product Sales'!C2:C2924))

It is a pretty straightforward SUM function nested with the INT function. As it references cells from a closed workbook, the entire path is included along with the cell references, worksheet name, and workbook name. However, if you need to nest some additional functions within this one, it will soon become very difficult to write.

Here is a quick way to write mega functions that reference external workbooks. The trick is to simply write the function in the workbook that you will be referencing in any spare cell. If you are going to be referencing only one worksheet in this workbook, it is best to use a cell on this worksheet.

First, using the method shown in Determine the Number of Specified Days in Any Month that explained an easy way to nest functions, simply develop the formula in any spare cell in the workbook that it will end up referencing. Once you have the desired result, cut the formula from the cell, activate the workbook in which the result should reside, select the appropriate cell, and paste.

Excel does all the hard work for you by including the workbook names and any sheet names. When/if you need to add or modify the formula, simply open the referenced workbook, cut the formula from the original workbook, and paste it into the referenced workbook. Then make your changes and cut and paste back to where it came from.