The `ADDRESS` function in Excel is used to generate a cell reference as a string based on a specified row and column number. This can be useful when you need to construct cell references dynamically. Here’s how you can use the `ADDRESS` function:
Syntax
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet])
- row_num: The row number of the cell reference.
- column_num: The column number of the cell reference.
- abs_num (optional): This specifies the type of reference to return. It can be:
- 1 or omitted: Absolute (e.g., $A$1)
- 2: Absolute row and relative column (e.g., A$1)
- 3: Relative row and absolute column (e.g., $A1)
- 4: Relative (e.g., A1)
- a1 (optional): A logical value that specifies the reference style.
- TRUE or omitted: Refers to A1 style.
- FALSE: Refers to R1C1 style.
- sheet (optional): Text specifying the sheet name to use in the external reference.
Examples
=ADDRESS(1, 1)
- Basic Usage
This returns `$A$1`, which is the absolute reference to the cell in the first row and the first column.
=ADDRESS(1, 1, 4)
- Relative Reference
This returns `A1`, a relative reference to the cell in the first row and the first column.
=ADDRESS(1, 1, 1, , "Sheet2")
- Use with Sheet Name
This returns `Sheet2!$A$1`, an absolute reference including a sheet name.
=ADDRESS(1, 1, 1, FALSE)
- R1C1 Reference Style
This returns `R1C1`, representing the R1C1 style reference.
Practical Example
If you want to create a dynamic reference that changes based on row and column inputs, you could combine `ADDRESS` with other functions:
=ADDRESS(B1, C1)
- Suppose you have row numbers in `B1` and column numbers in `C1`, and you want a string reference:
This setup can be very powerful for building formulas that adjust automatically when rows or columns are inserted or deleted.
Unlock Your Potential
Excel
Access
Power BI
