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.