Function Reference: COLUMN, ROW
| Syntax | ||
| =COLUMN([reference]) =ROW(reference]) =SHEET([value]) |
||
| Purpose / Description | ||
| Returns the column number (COLUMN), row number (ROW) or sheet number (SHEET) of the first cell in a referenced range. Usefully, if no reference is given, returns the column, row and sheet numbers respectively for the cell in which the formula is placed. | ||
| Arguments | ||
| Name | Type | Description |
| [reference] | reference | An optional reference or nested expression returning a reference to a range, for which the column number or row number of its first cell is of interest. May be omitted, in which case the column or row number of the cell in which the formula is placed will be derived. |
| [value] | reference or text string | An optional constant, reference or nested expression returning a reference to a range, or the name of a sheet, for which the sheet number is of interest. May be omitted, in which case the sheet number of the cell in which the formula is placed will be derived. |
| Return Values | ||
| Type | Specific Value | Description |
| number | Returns the column number (COLUMN), row number (ROW) or sheet number (SHEET) of the first cell in the referenced range, or if no reference is given of the cell in which the formula is stored. In the case of the SHEET function, it will also return the sheet number of the sheet whose name is passed as a text string. |
|
| error | #REF! | If the reference passed to the reference argument has more than one area, or the reference is derived by formula and the result of the formula is not a valid range. |
| error | #N/A | If the value passed to the value argument is not a reference or the name of a worksheet within the workbook. Numbers and logicals cause this result as well. |
| Notes / Comments / Advice / Warnings | ||
| Note: | Deriving the column number, row number or sheet number of a range containing errors does not result in the error values being returned, as these functions do not depend upon the cell value, but the location of the cell within a worksheet. | |
| Advice: | These functions are very useful in a number of referencing calculations where the target or source column / row / sheet for the calculation has to be derived dynamically. | |
| Examples: | Open / download sample workbook: AREAS, COLUMN, COLUMNS, ROW, ROWS, SHEET, SHEETS.xlsx | |