Excel ADDRESS Function
Summary
The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative or absolute format, and can be used to construct a cell reference inside a formula.
Purpose
Create a cell address from a given row and column
Return value
A cell address in the current or given worksheet.
Syntax
=ADDRESS (row_num, col_num, [abs_num], [a1], [sheet])
Arguments
- row_num - The row number to use in the cell address.
- col_num - The column number to use in the cell address.
- abs_num - [optional] The address type (i.e. absolute, relative). Defaults to absolute.
- a1 - [optional] The reference style, A1 vs R1C1. Defaults to A1 style.
- sheet - [optional] The name of the worksheet to use. Defaults to current sheet.
Usage notes
ADDRESS formula examples
To configure COUNTIFS (or COUNTIF) with a variable range, you can use the OFFSET function. In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")
This formula...
To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data),...
To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions.
In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data))
How...
To get cell content with a given row and column number, you can use the ADDRESS function together with INDIRECT. In the example shown, the formula in G6 is:
=INDIRECT(ADDRESS(G4,G5))
How this formula works
The Excel...
To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions.
In the example shown, the formula in C5 is:
=SUBSTITUTE(ADDRESS(1,B5,4),...
To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions.
In the example shown, the formula in G6 is:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data...
