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
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 fi 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
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 fi fth argument can accept a sheet name. In this case, Microsoft
examines the sheet name and fi 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
Atlanta!$B$5 .
The version of ADDRESS with the fi fth argument returns text that can be used
in the INDIRECT function. In Figure 30, the formula in cell D6 is:
=INDIRECT(ADDRESS(ROW(),2,4,1,D$3))
The fi 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 fi 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