Microsoft Office Tutorials and References

In Depth Information

**POINT TO ANOTHER WORKSHEET WITH INDIRECT**

Part

I

Figure 30.
Using
ADDRESS
is a bit more complicated than using
CELL
, but

it involves a shorter formula.

Additional Details:
There is a version of
INDIRECT
that works with
R1C1-

style
references because there are times when using
R1C1
is actually easier.

So far, all the examples of
INDIRECT
have used only a single argument.

Leaving off the second argument:

=INDIRECT("B4")

or specifying
TRUE
as the second argument:

=INDIRECT("B4",TRUE)

tells Excel to interpret the reference as an
A1-style
reference. Using
FALSE

as the second argument:

=INDIRECT("RC",False)

tells Excel to interpret the reference as an
R1C1
reference.

The reference
=RC
points to the current row and the current column. Including

a number after the
R
or
C
creates an absolute reference to a particular row or

column.
=RC2
is the
R1C1
method for referring to column B of this row. If you

use
R1C1
, you don’t have to worry about using
CELL
or
ADDRESS
. In Figure

31, the formula used in C7 is:

=INDIRECT("’"&C$3&"’!RC2",FALSE)

This formula can be copied throughout the table. Note that you do not have to

switch the worksheet to
R1C1
style in order to use this formula.

Figure 31.
R1C1
style is not popular, but it certainly makes this formula easier to write.

Summary
: With a little extra thought, you can use
INDIRECT
to point to another

worksheet.