Address Function in Excel


In Excel, the ADDRESS function returns a text representation of a cell address.
The syntax for the ADDRESS function is:
ADDRESS( row, column, [ref_type], [ref_style], [sheet_name] )
row is the row number to use in the cell address.
column is the column number to use in the cell address.
ref_type is optional. It is the type of reference to use. It can be any of the following values:
ValueExplanation
1Absolute referencing.
For example: $A$1
2Absolute row; relative column.
For example: $A1
3Relative row; absolute column.
For example: A$1
4Relative referencing.
For example: A1
If this parameter is omitted, the ADDRESS function assumes that the ref_type is set to 1.
ref_style is optional. It is the reference style to use: either A1 or R1C1. It can be any of the following values:
ValueExplanation
TRUEA1 style referencing
FALSER1C1 style referencing
If this parameter is omitted, the ADDRESS function assumes that the ref_style is set to TRUE.
sheet_name is optional. It is the name of the sheet to use in the cell address. If this parameter is omitted, then no sheet name is used in the cell address.

Applies To:

  • Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

  • Worksheet function (WS)

Worksheet Function Example:

Let's take a look at an example to see how you would use the ADDRESS function in a worksheet:
=ADDRESS(4, 5)would return "$E$4"
=ADDRESS(4, 5, 1)would return "$E$4"
=ADDRESS(4, 5, 2)would return "E$4"
=ADDRESS(4, 5, 3)would return "$E4"
=ADDRESS(4, 5, 4)would return "E4"
=ADDRESS(4, 5, 1, TRUE)would return "$E$4"
=ADDRESS(4, 5, 1, FALSE)would return "R4C5"
=ADDRESS(4, 5, 1, TRUE, "Sheet1")would return "Sheet1!$E$4"

Popular posts from this blog

50 Excel VBA Oral Interview Questions