ADDRESS

Makes a cell reference with given row and column

Syntax of Excel ADDRESS formula:
=ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])

In words:
Make address to cell with(this row number, column number, [this reference mode], [in this reference format,[with this sheet name])

Explanation

ADDRESS function is used to create a cell reference with bits and pieces. This is exactly what we do with this function. We provide row number and column number and it gives out a cell reference that can be used in any way we want.

Arguments of this function are briefly explained below:

row_num: this is a number that helps determine the row in Excel worksheet. By default Excel uses number to represent row. So its not much difficult.

column_num: this again is a number of column which you want to refer to. This can be a little tricky as by default Excel uses alphabets to name columns. So take due care while mentioning the number.

abs_num: It is an optional argument. This argument is used to mention the type of reference. Remember we have four different types of reference to make column or rows part relevant or absolute. It can be 1, 2, 3 or 4. Each number means:

1 or omitted Absolute both row and column e.g. $A$1
2 Absolute row; relative column e.g. A$1
3 Relative row; absolute column e.g. $A1
4 Relative both row and column e.g. A1

If this argument is omitted then Excel assumes it to be 1.

[a1]: this is an optional argument and is used to dictate reference style. Most of us are familiar with A1 style in which column and row are mentioned. For example H7. However there is a second style as well known as “R1C1”. TRUE will refer to A1 style and FALSE will refer to R1C1 style. If it is omitted Excel will assume it to be TRUE.

[sheet_text]: This is also an optional argument and used to refer to specific worksheet that may be in the same workbook or another workbook thus making reference to external sources easy. If it is omitted Excel assume it to be current worksheet.

Example of ADDRESS Formula

Must read: 5 Ways to Transpose in Excel – Shifting Columns to Rows or Rows to Columns [Section: Transpose – Rows to Columns using INDIRECT]

=ADDRESS(7,6) will return $F$7

=ADDRESS(7,6,2) will return F$7