The address of the first and last cell in Excel range


This module illustrates:

  1. Use of selected functions from Excel's lookup and reference function category, and CELL function from the information category
  2. With examples of the:
    • Address of the first cell in a range
    • Address of the last cell in a range
    • Address of the range
    • Value in the first cell in a range
    • Value in the last cell in a range
  3. Descriptions of the function syntax

Sample data and functions examples


Fig 1: Excel Web App #1 - functions that return information about the first and last cells in a range

 

Function syntax


Excel functionDescription
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Returns the address of a cell in a worksheet, given specified row and column numbers
Arguments:
row_num: Required - row number
column_num: Required - column number
abs_num: Optional - a numeric value that specifies the return reference
1 or omitted - Absolute;
2 - Absolute row, and relative column;
3 - Relative row, and absolute column; and
4 - Relative
a1: Optional - a logical value that specifies A1 style or R1C1 style
TRUE [1] or omitted - A1 style; or
FALSE [0] - R1C1 style
sheet_text: Optional - a text value that specifies the name of an external worksheet
Refers to a cell on current sheet if omitted
CELL(info_type, [reference])
Returns information about the location, contents, or formatting of a cell
Arguments (not a complete list):
info_type: Required - text that specifies the type of information to return
"address" - returns a text expression address of the first cell in reference;
"col" - returns column number of the first cell in the reference;
"contents" - returns the value of the first cell in the reference;
"row" - returns the row number of the first cell in the reference
Note: the first cell is the upper left cell in the reference range.
COLUMN([reference]) Returns the column number of the given cell reference, or if reference is omitted, the column number of the cell where the formula appears
COLUMNS(array) Returns the number of columns in an array or reference
INDEX(array, row_num, [column_num])
Array form 		Returns the value of an element in a table or an array, selected by the row and column number indexes (r,c). If INDEX is a nested function and used in a position that requires a reference parameter, then INDEX returns the reference
Arguments:
row_num: Required - row number
column_num: Optional - column number
INDIRECT(ref_text, [a1]) Returns the reference specified by a text string.
Arguments:
ref_text: Required - reference to a cell with value in A1-reference style
a1: Optional - a logical value that specifies what type of reference
TRUE or omitted - A1 style; or
FALSE - R1C1 style
ROW([reference]) Returns the row number of the given cell reference, or if reference is omitted, the row number of the cell where the formula appears
ROWS(array) Returns the number of rows in an array or reference