Microsoft Office Tutorials and References
In Depth Information
POINT TO ANOTHER WORKSHEET WITH INDIRECT
Solution: Figure 28 shows six branch worksheets. Each has data in column B.
The goal is to write a formula in row 4 that will pull data from column B of the
various worksheets, based on the labels in row 3. The formula in cell G4 in
Figure 28 works ﬁ ne for row 4:
Note: This formula includes the extra hassle of using apostrophes, even though
only one of the worksheets, Eden Prairie, contains a space.
Figure 28. h ese INDIRECT function points to a variable worksheet.
However, the problem with the formula in G4 is that it is hard-coded to grab
data from B4, so it cannot automatically copy to rows 5 through 7. To allow the
formula to grab data from other rows, you can use the CELL function or the
ADDRESS function. Both of these methods work. You can be up and running
using CELL in a matter of seconds, but ADDRESS might ultimately be easier,
once you understand the nuances of using it.
=CELL("address",$B4) returns the text $B$4 . This is perfect for inserting
in the INDIRECT function. The dollar sign before the B makes sure that the
formula points to column B on each worksheet. The lack of a dollar sign before
the 4 in $B4 allows the formula to point to row 5, 6, 7, and so on as you copy
down. In Figure 29, the formula in cell C5 is:
This formula can be copied throughout the table.
Figure 29. Adding CELL allows you to copy one formula throughout the table.