Abdul Alim: a little bit learning, let's go

Excel, VBA and Power BI tutorials 

ADDRESS Formula

Definition:

The Excel formula “ADDRESS” returns a cell reference as a text string, based on the row and column number of a cell.

Purpose:

The purpose of the “ADDRESS” formula is to get the address of a cell in a text format.

Syntax:

The syntax of the “ADDRESS” formula is

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Parameters:

• row_num: Required. It specifies the row number of the cell reference.

• column_num: Required. It specifies the column number of the cell reference.

• abs_num: Optional. It specifies the type of reference, i.e., relative, or absolute. Default is 1. Below is the list of other abs_num

  • 1 : Absolute referencing. For example: $A$1

  • 2 : Relative column; absolute row. For example: A$1

  • 3 : Absolute column; relative row. For example: $A1

  • 4 : Relative referencing. For example: A1

• a1: Optional. It specifies the reference style, i.e., A1 or R1C1. Default is TRUE.

• sheet_text: Optional. It specifies the sheet name of the cell reference. Default is the current sheet.

Returns:

The “ADDRESS” formula returns a cell reference in a text format.

Availability:

The “ADDRESS” formula is available in all versions of Excel.

Example-1:

=ADDRESS(1, 1)

returns $A$1.

Example-2:

=ADDRESS(3, 2, , , “Sheet2”)

returns Sheet2!$B$3.

Example-3:

=ADDRESS(1, 2, 4)

returns $B$1.

Example-4:

=ADDRESS(3, 3, 1, FALSE)

returns C3.

Example-5:

=ADDRESS(1, 1, 4, TRUE, "Sheet2")

returns ‘Sheet2’!$A$1.

Tips and tricks:

• Use the “ADDRESS” formula with other formulas to make dynamic formulas.

• Use the “INDIRECT” formula with “ADDRESS” to get the value of a cell whose reference is in a text format.

Limitations:

The “ADDRESS” formula returns a static cell reference, which means it does not change if you copy or move the formula.

Common errors and solutions:

• #VALUE! error: Occurs when the row_num or column_num is not a valid number. Check if the values are correct.

• #REF! error: Occurs when the abs_num is greater than 4. Check the abs_num value.

List of Related functions:

• ROW: Returns the row number of a cell.

• COLUMN: Returns the column number of a cell.

• INDIRECT: Returns the value of a cell whose reference is in a text format.

• OFFSET: Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.

Frequently Used with the formulas:

The “ADDRESS” formula is frequently used with other formulas like “INDIRECT“, “SUMIF“, and “VLOOKUP” to create dynamic formulas.

Frequently Asked Questions:

Q: What is the purpose of the “abs_num” parameter in the “ADDRESS” formula?
A: The “abs_num” parameter specifies the type of reference, i.e., relative, or absolute.

Q: Can the “ADDRESS” formula return the cell reference in R1C1 style?

A: Yes, by setting the a1 parameter to FALSE, you can get the cell reference in R1C1 style.