Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 79 Hack 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 simplfiy what can be a complex
process.

Writing such formulas from scratch can become unwieldy quickly. In
this hack, 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:\Ozgrid Likom\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 [Hack #77] 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.