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:

=INDIRECT("’"&G$3&"’!"&"B4")

Note: This formula includes the extra hassle of using apostrophes, even though

only one of the worksheets, Eden Prairie, contains a space.

Part

I

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:

=INDIRECT("’"&C$3&"’!"&CELL("address",$B5))

This formula can be copied throughout the table.

Figure 29.
Adding
CELL
allows you to copy one formula throughout the table.