Microsoft Office Tutorials and References
In Depth Information
Using ADDRESS to Find the Address for Any Cell
The OFFSET function initially seems intimidating, especially in light of the
example you just walked through. Remember that for useful results from
OFFSET, you usually replace one or more of the final four arguments with
to Find the Address for Any Cell
If someone asks you for the cell address for the cell in row 5, column 5, you
could probably come up with E5 quickly. What if someone asks you for the
cell address of the cell in row 26, column 26? This is Z26. Again, you should
come up with this if you know there are 26 letters in the alphabet.
If someone asks you to calculate the address of row 2 and column 30, you
have to divide 30 by 26 to learn that the result is 1 with a remainder of 4.
This could lead you to conclude the cell address is the first letter of the
alphabet (A) and the fourth letter of the alphabet (D) to come up with AD2.
This type of calculation becomes far more complex with 16,384 columns. For
example, how would you calculate the address for row 2 of column 14123?
Fortunately, Excel provides the ADDRESS function to convert any intersec-
tion of row and column number to an address. =ADDRESS(2,14123) returns the
text of $TWE$2.
ADDRESS to Find the Address for Any Cell
The default version of ADDRESS returns the cell address as an absolute
address with both dollar signs. There are optional parameters to control
row_num — This is the row number to use in the cell reference.
column_num — This is the column number to use in the cell reference.
abs_num — This specifies the type of reference to return. If it is 1 or
omitted, the returned address has both dollar signs and is absolute.
If it is 2, the row is held absolute, but the column is relative. If it is
3, the row is relative and the column is absolute. If it is 4, the ad-
dress is relative, with no dollar signs.
• aa1 — This is a logical value that specifies the A1 or R1C1 reference
style. If a1is TRUE or omitted, ADDRESS returns an A1-style referen-
ce; if it is FALSE, ADDRESS returns an R1C1-style reference.