Excel ADDRESS Function
The Excel ADDRESS function returns a cell reference as a string, based on a row and column number
Example: Excel ADDRESS Function
|
=ADDRESS(3,4)
|
Result in cell G5 ($D$3) - returns the assigned cell reference that comprises only the required ADDRESS arguments (row number and column number)) as a string.
|
|
=ADDRESS(3,4,1)
|
Result in cell G6 ($D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and an abs number (Absolute row and column) as a string.
|
|
=ADDRESS(3,4,2)
|
Result in cell G7 (D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and an abs number (Absolute row and Relative column) as a string.
|
|
=ADDRESS(3,4,3)
|
Result in cell G8 ($D3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and an abs number (Relative row and Absolute column) as a string.
|
|
=ADDRESS(3,4,4)
|
Result in cell G9 (D3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and an abs number (Relative row and column) as a string.
|
|
=ADDRESS(3,4,1,TRUE)
|
Result in cell G10 ($D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number), abs number (Absolute row and column) and A1 reference style as a string.
|
|
=ADDRESS(3,4,,TRUE)
|
Result in cell G11 ($D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and A1 reference style as a string.
|
|
=ADDRESS(3,4,1,FALSE)
|
Result in cell G12 (R3C3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number), abs number (Absolute row and column) and R1C1 reference style as a string.
|
|
=ADDRESS(3,4,1,TRUE,"Sheet1")
|
Result in cell G13 (Sheet1!$D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number), abs number (Absolute row and column), A1 reference style and reference a sheet name as a string.
|
METHOD 2. Excel ADDRESS function using links
EXCEL
|
=ADDRESS(B5,C5)
|
Result in cell G5 ($D$3) - returns the assigned cell reference that comprises only the required ADDRESS arguments (row number and column number)) as a string.
|
|
=ADDRESS(B6,C6,D6)
|
Result in cell G6 ($D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and an abs number (Absolute row and column) as a string.
|
|
=ADDRESS(B7,C7,D7)
|
Result in cell G7 (D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and an abs number (Absolute row and Relative column) as a string.
|
|
=ADDRESS(B8,C8,D8)
|
Result in cell G8 ($D3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and an abs number (Relative row and Absolute column) as a string.
|
|
=ADDRESS(B9,C9,D9)
|
Result in cell G9 (D3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and an abs number (Relative row and column) as a string.
|
|
=ADDRESS(B10,C10,D10,E10)
|
Result in cell G10 ($D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number), abs number (Absolute row and column) and A1 reference style as a string.
|
|
=ADDRESS(B11,C11,,E11)
|
Result in cell G11 ($D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number) and A1 reference style as a string.
|
|
=ADDRESS(B12,C12,D12,E12)
|
Result in cell G12 (R3C3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number), abs number (Absolute row and column) and R1C1 reference style as a string.
|
|
=ADDRESS(B13,C13,D13,E13,F13)
|
Result in cell G13 (Sheet1!$D$3) - returns the assigned cell reference that comprises the required ADDRESS arguments (row number and column number), abs number (Absolute row and column), A1 reference style and reference a sheet name as a string.
|
METHOD 3. Excel ADDRESS function using the Excel built-in function library with hardcoded value
EXCEL
Formulas tab > Function Library group > Lookup & Reference > ADDRESS > populate the input boxes
| = ADDRESS(3,4,1,TRUE,"Sheet1") Note: in this example we are populating all of the input boxes associated with the ADDRESS function arguments, however, you are only required to populate the required arguments (Row_num and Column_num). You can omit the optional arguments and Excel will apply its default value against each of them. |
 |
METHOD 4. Excel ADDRESS function using the Excel built-in function library with links
EXCEL
Formulas tab > Function Library group > Lookup & Reference > ADDRESS > populate the input boxes
| = ADDRESS(B13,C13,D13,E13,F13) Note: in this example we are populating all of the input boxes associated with the ADDRESS Function arguments with links, however, you are only required to populate the required arguments (Row_num and Column_num). You can omit the optional arguments and Excel will apply the default value against each of them. |
 |
Sub Excel_ADDRESS_Function_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("ADDRESS")
'apply the Excel ADDRESS function
ws.Range("G5") = ws.Cells(3, 4).Address()
ws.Range("G6") = ws.Cells(3, 4).Address(RowAbsolute:=True)
ws.Range("G7") = ws.Cells(3, 4).Address(ColumnAbsolute:=False)
ws.Range("G8") = ws.Cells(3, 4).Address(RowAbsolute:=False)
ws.Range("G9") = ws.Cells(3, 4).Address(RowAbsolute:=False, ColumnAbsolute:=False)
ws.Range("G10") = ws.Cells(3, 4).Address(RowAbsolute:=True, ReferenceStyle:=xlA1)
ws.Range("G11") = ws.Cells(3, 4).Address(ReferenceStyle:=xlA1)
ws.Range("G12") = ws.Cells(3, 4).Address(RowAbsolute:=True, ReferenceStyle:=xlR1C1)
ws.Range("G13") = "'" & ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Parent.Name & "'!" & ws.Cells(3, 4).Address(RowAbsolute:=True)
ws.Range("G5") = ws.Cells(3, 4).Address()
ws.Range("G6") = ws.Cells(3, 4).Address(RowAbsolute:=True)
ws.Range("G7") = ws.Cells(3, 4).Address(ColumnAbsolute:=False)
ws.Range("G8") = ws.Cells(3, 4).Address(RowAbsolute:=False)
ws.Range("G9") = ws.Cells(3, 4).Address(RowAbsolute:=False, ColumnAbsolute:=False)
ws.Range("G10") = ws.Cells(3, 4).Address(RowAbsolute:=True, ReferenceStyle:=xlA1)
ws.Range("G11") = ws.Cells(3, 4).Address(ReferenceStyle:=xlA1)
ws.Range("G12") = ws.Cells(3, 4).Address(RowAbsolute:=True, ReferenceStyle:=xlR1C1)
ws.Range("G13") = "'" & ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Parent.Name & "'!" & ws.Cells(3, 4).Address(RowAbsolute:=True)
End Sub
OBJECTS
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
PREREQUISITES
Worksheet Name: Have a worksheet named ADDRESS.
Worksheet Name: Have a worksheet named ADDRESS.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("G5"), ("G6"), ("G7"), ("G8"), ("G9"), ("G10"), ("G11"), ("G12") and ("G13") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 2. Excel ADDRESS function using VBA with links
VBA
Sub Excel_ADDRESS_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("ADDRESS")
'apply the Excel ADDRESS function
ws.Range("G5") = ws.Cells(Range("B5"), Range("C5")).Address()
ws.Range("G6") = ws.Cells(Range("B6"), Range("C6")).Address(RowAbsolute:=True)
ws.Range("G7") = ws.Cells(Range("B7"), Range("C7")).Address(ColumnAbsolute:=False)
ws.Range("G8") = ws.Cells(Range("B8"), Range("C8")).Address(RowAbsolute:=False)
ws.Range("G9") = ws.Cells(Range("B9"), Range("C9")).Address(RowAbsolute:=False, ColumnAbsolute:=False)
ws.Range("G10") = ws.Cells(Range("B10"), Range("C10")).Address(RowAbsolute:=True, ReferenceStyle:=xlA1)
ws.Range("G11") = ws.Cells(Range("B11"), Range("C11")).Address(ReferenceStyle:=xlA1)
ws.Range("G12") = ws.Cells(Range("B12"), Range("C12")).Address(RowAbsolute:=True, ReferenceStyle:=xlR1C1)
ws.Range("G13") = "'" & ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Parent.Name & "'!" & ws.Cells(Range("B13"), Range("C13")).Address(RowAbsolute:=True)
ws.Range("G5") = ws.Cells(Range("B5"), Range("C5")).Address()
ws.Range("G6") = ws.Cells(Range("B6"), Range("C6")).Address(RowAbsolute:=True)
ws.Range("G7") = ws.Cells(Range("B7"), Range("C7")).Address(ColumnAbsolute:=False)
ws.Range("G8") = ws.Cells(Range("B8"), Range("C8")).Address(RowAbsolute:=False)
ws.Range("G9") = ws.Cells(Range("B9"), Range("C9")).Address(RowAbsolute:=False, ColumnAbsolute:=False)
ws.Range("G10") = ws.Cells(Range("B10"), Range("C10")).Address(RowAbsolute:=True, ReferenceStyle:=xlA1)
ws.Range("G11") = ws.Cells(Range("B11"), Range("C11")).Address(ReferenceStyle:=xlA1)
ws.Range("G12") = ws.Cells(Range("B12"), Range("C12")).Address(RowAbsolute:=True, ReferenceStyle:=xlR1C1)
ws.Range("G13") = "'" & ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Parent.Name & "'!" & ws.Cells(Range("B13"), Range("C13")).Address(RowAbsolute:=True)
End Sub
OBJECTS
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
PREREQUISITES
Worksheet Name: Have a worksheet named ADDRESS.
Worksheet Name: Have a worksheet named ADDRESS.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("G5"), ("G6"), ("G7"), ("G8"), ("G9"), ("G10"), ("G11"), ("G12") and ("G13") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 3. Excel ADDRESS function using VBA with ranges
VBA
Sub Excel_ADDRESS_Function_Using_Ranges()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("ADDRESS")
'apply the Excel ADDRESS function
ws.Range("G5") = ws.Range("D3").Address()
ws.Range("G6") = ws.Range("D3").Address(RowAbsolute:=True)
ws.Range("G7") = ws.Range("D3").Address(ColumnAbsolute:=False)
ws.Range("G8") = ws.Range("D3").Address(RowAbsolute:=False)
ws.Range("G9") = ws.Range("D3").Address(RowAbsolute:=False, ColumnAbsolute:=False)
ws.Range("G10") = ws.Range("D3").Address(RowAbsolute:=True, ReferenceStyle:=xlA1)
ws.Range("G11") = ws.Range("D3").Address(ReferenceStyle:=xlA1)
ws.Range("G12") = ws.Range("D3").Address(RowAbsolute:=True, ReferenceStyle:=xlR1C1)
ws.Range("G13") = "'" & ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Parent.Name & "'!" & ws.Range("D3").Address(RowAbsolute:=True)
ws.Range("G5") = ws.Range("D3").Address()
ws.Range("G6") = ws.Range("D3").Address(RowAbsolute:=True)
ws.Range("G7") = ws.Range("D3").Address(ColumnAbsolute:=False)
ws.Range("G8") = ws.Range("D3").Address(RowAbsolute:=False)
ws.Range("G9") = ws.Range("D3").Address(RowAbsolute:=False, ColumnAbsolute:=False)
ws.Range("G10") = ws.Range("D3").Address(RowAbsolute:=True, ReferenceStyle:=xlA1)
ws.Range("G11") = ws.Range("D3").Address(ReferenceStyle:=xlA1)
ws.Range("G12") = ws.Range("D3").Address(RowAbsolute:=True, ReferenceStyle:=xlR1C1)
ws.Range("G13") = "'" & ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Parent.Name & "'!" & ws.Range("D3").Address(RowAbsolute:=True)
End Sub
OBJECTS
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
PREREQUISITES
Worksheet Name: Have a worksheet named ADDRESS.
Worksheet Name: Have a worksheet named ADDRESS.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("G5"), ("G6"), ("G7"), ("G8"), ("G9"), ("G10"), ("G11"), ("G12") and ("G13") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
DESCRIPTION
The Excel ADDRESS function returns a cell reference as a string, based on a row and column number.
The Excel ADDRESS function returns a cell reference as a string, based on a row and column number.
SYNTAX
=ADDRESS(row_num, column_num, [abs_num],[a1],[sheet_text])
=ADDRESS(row_num, column_num, [abs_num],[a1],[sheet_text])
ARGUMENT(S)
row_num: (Required) Row number to use in the reference.
column_num: (Required) Column number to use in the reference.
abs_num: (Optional) Type of address reference to use. This can be any of the following values:
| Value | Explanation | Example |
|---|---|---|
| 1 | Absolute row and column | $A$1 |
| 2 | Absolute row and Relative column | A$1 |
| 3 | Relative row and Absolute column | $A1 |
| 4 | Relative row and column | A1 |
Note: If the abs_num argument is omitted, the default value is 1 (Absolute row and column).
a1: (Optional) Specifies what type of reference style to use. This can be any of the following:
| Value | Explanation | Example |
|---|---|---|
| TRUE | A1 reference style | A1, A2, B2 |
| FALSE | R1C1 reference style | R1C1, R2C1, R2C2 |
Note: If the a1 argument is omitted, the default value is TRUE (A1 reference style).
sheet_text: (Optional) Specifies the name of the worksheet to be used. You will need to insert the name between the quotation marks ("Name").
Note: If the sheet_text argument is omitted, no sheet name will appear.