Part IV: Managing and Analyzing Data
You can include any number of external references in this formula, up to the 8,000-charac-
ter limit for a formula. However, if you use many external references, such a formula can be
quite lengthy and confusing if you need to edit it.
If the worksheets that you’re consolidating aren’t laid out in the same way, you can still
use formulas, but you need to ensure that each formula refers to the correct cell—a task
that is both tedious and error prone.
Consolidating worksheets by using Paste Special
Another method of consolidating information is to use the Paste Special dialog box. This
technique takes advantage of the fact that the Paste Special dialog box can perform a
mathematical operation when it pastes data from the Clipboard. For example, you can use
the Add option to add the copied data to the selected range. Figure 28.5 shows the Paste
Special dialog box.
FIGURE 28.5
Choosing the Add operation in the Paste Special dialog box
This method is applicable only when all the worksheets that you’re consolidating are open.
The disadvantage is that the consolidation isn’t dynamic. In other words, it doesn’t gener-
ate formulas that refer to the original source data. So, if any data that was consolidated
changes, the consolidation is no longer accurate.
Here’s how to use this method:
- Copy the data from the first source range.
- Activate the dependent workbook and select a location for the consolidated
data. A single cell is sufficient.
