Microsoft Office Tutorials and References
In Depth Information
POINT TO ANOTHER WORKSHEET WITH INDIRECT
You can use the ADDRESS function instead of the CELL function. In its simplest
=ADDRESS(5, 2) returns the text \$B\$5 . Initially, it might seem more complex
to write =ADDRESS(ROW(),2) instead of using CELL in order to refer to
column B in the current row. However, ADDRESS offers three additional optional
arguments:
Note: The third and fourth arguments do not help you in this topic, but you
have to learn them so that you can get to the ﬁ fth argument.
The third argument controls whether and where dollar signs appear in the
address. Here is an easy way to remember how this argument works:. The
number in the argument corresponds to how many times you press the F4
key to achieve the combination of dollar signs:
»
»
»
»
=ADDRESS(5,2,2) locks only the row (B\$5).
=ADDRESS(5,2,3) locks only the column (\$B5).
The fourth argument controls whether you get an A1-style reference or an
R1C1-reference:
=ADDRESS(5,2,1,1) return the A1-style reference \$B\$5.
=ADDRESS(5,2,1,0) return the R1C1 reference R5C2.
The ﬁ fth argument can accept a sheet name. In this case, Microsoft
examines the sheet name and ﬁ gures out whether you need apostrophes:
=ADDRESS(5,2,4,1,"Eden Prairie") returns ‘Eden Prairie’!B5 .
Note: You don’t really have to remember how the third and fourth arguments
work. If you simply use =ADDRESS(5,2,,,"Atlanta") , Excel returns
Atlanta!\$B\$5 .
The version of ADDRESS with the ﬁ fth argument returns text that can be used
in the INDIRECT function. In Figure 30, the formula in cell D6 is:
The ﬁ rst argument in ADDRESS is ROW() , which ensures that Excel grabs the
row where the formula is. The second argument is hard-coded to a 2 to make
sure you always get column B. The third and fourth arguments return a relative
A1-style reference. The ﬁ fth argument contains only a dollar sign before the
row to make sure you always get a sheet name from row 3, but the column can
change as the formula is copied.
»
»
»
»

Search JabSto ::

Custom Search