Microsoft Office Tutorials and References
In Depth Information
POINT TO ANOTHER WORKSHEET WITH INDIRECT
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:
or specifying TRUE as the second argument:
tells Excel to interpret the reference as an A1-style reference. Using FALSE
as the second argument:
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:
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