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
form, =ADDRESS(Row,Column) returns a cell address. For example,
=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
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,1) gives you $B$5.
=ADDRESS(5,2,2) locks only the row (B$5).
=ADDRESS(5,2,3) locks only the column ($B5).
=ADDRESS(5,2,4) locks nothing (=B5) .
The fourth argument controls whether you get an A1-style reference or an
=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,"Atlanta") returns Atlanta!B5 .
=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
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.