|
The Excel ADDRESS Function
Home »
Excel-Built-In-Functions »
Excel-Lookup-And-Reference-Functions »
Excel-Address-Function
Search this site:
Basic Description
The Excel Address function takes row and column numbers and returns a cell reference as a text string.
The syntax of the function is:
ADDRESS( row_num, column_num, [abs_num], [a1], [sheet_text] )
where the arguments are as follows:
| row_num |
- |
The row number to use in the reference
|
| column_num |
- |
The column number to use in the reference
|
| [abs_num] |
- |
An optional argument, which specifies whether the reference should contain absolute or relative references.
abs_num can take on the following values :
| 1 - denotes absolute row & column reference |
| 2 - denotes absolute row & relative column reference |
| 3 - denotes relative row & absolute column reference |
| 4 - denotes relative row & column reference |
If this argument is omitted, it takes on the default value 1 (absolute references)
|
| [a1] |
- |
An optional logical argument that defines the style of the returned reference.
This can be either :
- True - to denote that the reference is in A1 style
- False - to denote that the reference is in R1C1 style
If this argument is omitted, it takes on the default value "True"
|
| [sheet_text] |
- |
An optional text argument that specifies the sheet name.
If this argument is omitted, the sheet name is blank (ie. the reference relates to the current worksheet)
|
Address Function Examples
The following spreadsheets show simple examples of the Excel Address function.
The format of the function is shown in the spreadsheet on the left and the results
are shown in the spreadsheet on the right.
Formulas:
|
Results:
|
Note that cell A5 in the above spreadsheet combines the ADDRESS function with the
ROW function and the
COLUMN function to
display the reference to the current cell.
Further information and examples of the Excel Address function can be found on the
Microsoft Office website.
Address Function Error
If you get an error from the Excel Address function, this is likely to be the #VALUE! error :
Common Error
|
#VALUE!
|
-
|
Occurs if the supplied arguments are not recognised as the accepted values. I.e. if either:
| - |
The row_num is less than 1 or greater than the number of rows in the spreadsheet;
|
| - |
The column_num is less than 1 or greater than the number of columns in the spreadsheet;
|
| or |
|
| - |
Any of the supplied row_num, column_num or [abs_num] arguments are non-numeric or the
supplied [a1] argument is not recognised as a logical value.
|
|
|
