Difference between revisions of "Documentation/How Tos/Calc: ADDRESS function"

From Apache OpenOffice Wiki
Jump to: navigation, search
m
 
(11 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
__NOTOC__
+
{{DISPLAYTITLE:ADDRESS function}}
 +
{{Documentation/CalcFunc SpreadsheetTOC
 +
|ShowPrevNext=block
 +
|PrevPage=Documentation/How_Tos/Calc:_Spreadsheet_functions
 +
|NextPage=Documentation/How_Tos/Calc:_CHOOSE_function
 +
}}__NOTOC__
  
 
== ADDRESS  ==
 
== ADDRESS  ==
Line 5: Line 10:
  
 
=== Syntax: ===
 
=== Syntax: ===
−
<tt>'''ADDRESS(row; column; mode; sheet)'''</tt>
+
<tt>'''ADDRESS(row; column; mode; type; sheet)'''</tt>
  
 
: <tt>'''row'''</tt> is a <u>number</u> specifying the row.
 
: <tt>'''row'''</tt> is a <u>number</u> specifying the row.
Line 12: Line 17:
  
 
: <tt>'''mode'''</tt> (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be <tt>'''1'''</tt>.
 
: <tt>'''mode'''</tt> (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be <tt>'''1'''</tt>.
 +
 
<blockquote>
 
<blockquote>
 
{| border="0" cellpadding="0" cellspacing="10" align="left"
 
{| border="0" cellpadding="0" cellspacing="10" align="left"
 
|-valign="top" align="center"
 
|-valign="top" align="center"
−
|'''mode'''
+
!mode||row||column||example'''
−
|'''row'''
+
−
|'''column'''
+
−
|'''example'''
+
−
 
+
 
|-valign="top" align="center"
 
|-valign="top" align="center"
−
|<tt>'''1'''</tt>
+
|<tt>'''1'''</tt>||absolute||absolute||$A$1
−
|absolute
+
−
|absolute
+
−
|$A$1
+
−
 
+
 
|-valign="top" align="center"
 
|-valign="top" align="center"
−
|<tt>'''2'''</tt>
+
|<tt>'''2'''</tt>||absolute||relative||A$1
−
|absolute
+
−
|relative
+
−
|A$1
+
−
 
+
 
|-valign="top" align="center"
 
|-valign="top" align="center"
−
|<tt>'''3'''</tt>
+
|<tt>'''3'''</tt>||relative||absolute||$A1
−
|relative
+
−
|absolute
+
−
|$A1
+
−
 
+
 
|-valign="top" align="center"
 
|-valign="top" align="center"
−
|<tt>'''4'''</tt>
+
|<tt>'''4'''</tt>||relative||relative||A1
−
|relative
+
−
|relative
+
−
|A1
+
 
|}
 
|}
 
<br style="clear:both;" />
 
<br style="clear:both;" />
 
</blockquote>
 
</blockquote>
  
−
: <tt>'''sheet'''</tt> is an optional <u>text string</u> specifying the sheet.
+
: <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''ADDRESS'''</tt> returns [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''ADDRESS'''</tt> returns the usual A1 notation.
 +
 
 +
: <tt>'''sheet'''</tt> is an optional <u>text string</u> specifying the sheet. In the usual A1 notation, Calc separates the sheet name with a <tt>'''.'''</tt> character; in the R1C1 notation, Calc separates the sheet name with a <tt>'''!'''</tt> character.
  
 
=== Example: ===
 
=== Example: ===
−
<tt>'''ADDRESS(4; 3; 2; "Sheet2")'''</tt>
+
<tt>'''ADDRESS(4; 3; 2;; "Sheet2")'''</tt>
−
: returns the text <tt>'''Sheet2.C$4'''</tt>.
+
: returns the text <tt>'''Sheet2.C$4'''</tt>. Note the omitted <tt>'''type'''</tt> parameter.
  
 
<tt>'''ADDRESS(4; 3; 4)'''</tt>
 
<tt>'''ADDRESS(4; 3; 4)'''</tt>
Line 59: Line 48:
 
: returns the text <tt>'''$C$4'''</tt>.
 
: returns the text <tt>'''$C$4'''</tt>.
  
−
=== See also: ===
+
<tt>'''ADDRESS(4; 3; 2; 0; "Sheet2")'''</tt>
−
'''[[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]]'''
+
: returns the text <tt>'''Sheet2!R4C[3]'''</tt>.
−
 
+
−
'''[[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
+
−
 
+
−
[[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
+
−
[[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]
+
  
 
=== Issues: ===
 
=== Issues: ===
−
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''ADDRESS(1;1;4;"Sheet2"))'''</tt> returns <tt>'''Sheet2.A1'''</tt> in Calc; the equivalent in Excel returns <tt>'''Sheet2!A1'''</tt>.
+
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''ADDRESS(1;1;4;;"Sheet2")'''</tt> returns <tt>'''Sheet2.A1'''</tt> in Calc; the equivalent in Excel returns <tt>'''Sheet2!A1'''</tt>.
−
*Excel allows an R1C1 reference style; this will work in Calc from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
+
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see {{bug|91020}}.
−
*The draft ODFF standard says that the default <tt>'''mode'''</tt> (if <tt>'''mode'''</tt> is omitted) should be <tt>'''4'''</tt>. Calc and Excel both default to <tt>'''1'''</tt>, so this may be a problem with ODFF.
+
*When storing a document in the older ODF 1.0/1.1 format, any <tt>'''type'''</tt> parameter in an <tt>'''ADDRESS'''</tt> function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. From OOo 3.0, Calc writes in ODF 1.2 draft format as standard, although this can be changed ({{menu|Tools|Options|Load/Save|General}}). A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened.
−
 
+
−
'''Additional optional parameter for OpenOffice.org 3:'''
+
−
 
+
−
For interoperability the ADDRESS and INDIRECT spreadsheet functions
+
−
now support an additional optional parameter to specify whether the
+
−
R1C1 address notation instead of the usual A1 notation should be used.
+
−
Though the R1C1 notation otherwise is not supported by the application
+
−
yet, this enables imported spreadsheet documents to calculate formulas
+
−
using it.
+
−
 
+
−
In ADDRESS, the parameter is _inserted_ as the 4th parameter, shifting
+
−
the optional sheet name parameter to the 5th position.
+
−
 
+
−
In INDIRECT, the parameter is appended as the 2nd parameter.
+
−
 
+
−
In both functions, if the argument is given and 0 the R1C1 notation is
+
−
used, if the argument is not given or has a value other than 0, the A1
+
−
notation is used. In case of R1C1 notation, ADDRESS produces address
+
−
strings using the exclamation mark '!' as the sheet name separator and
+
−
INDIRECT expects the exclamation mark as sheet name separator. Both
+
−
functions still use the dot '.' sheet name separator with A1 notation.
+
−
 
+
−
When opening documents stored in ODF 1.0 or 1.1 format, an ADDRESS
+
−
function appearing in a formula expression gets a 4th parameter of
+
−
value 1 inserted if a sheet name was given as 4th parameter, shifting
+
−
the sheet name to the 5th parameter.
+
−
 
+
−
When storing a document in ODF 1.0/1.1 format, if in an ADDRESS
+
−
function a 4th parameter is present that parameter's expression will
+
−
be stripped and not written.
+
−
NOTE! This causes incompatibilities if the argument's expression
+
−
calculated to 0, the function when loaded again will calculate a
+
−
different result! A document should not be stored in the old ODF
+
−
1.0/1.1 format if the ADDRESS function's new 4th parameter was used
+
−
with a value of 0.
+
  
−
The INDIRECT function is written as is to ODF 1.0/1.1 format; if the
+
{{SeeAlso|EN|
−
2nd parameter was present, an older version of Calc will return an
+
* [[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]]'''
−
error for that function. This is on purpose, as ADDRESS usually is
+
−
used in conjunction with INDIRECT, and when stored to ODF 1.0/1.1
+
−
format this combination will more likely return an error result upon
+
−
recalculation if the R1C1 notation was used.
+
  
−
This change aligns with the behavior of other spreadsheet applications and
+
* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
−
the definition given in the OASIS ODFF/OpenFormula specification
+
* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
−
available at
+
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
−
http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula
+
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 +
[[Category: Documentation/Reference/Calc/Spreadsheet functions]]

Latest revision as of 10:52, 2 February 2024



ADDRESS

Returns a cell reference as text, given row and column numbers.

Syntax:

ADDRESS(row; column; mode; type; sheet)

row is a number specifying the row.
column is a number (not a letter) specifying the column.
mode (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be 1.
mode row column example
1 absolute absolute $A$1
2 absolute relative A$1
3 relative absolute $A1
4 relative relative A1


type is an optional number; if 0, ADDRESS returns R1C1 notation, and otherwise (or if omitted) ADDRESS returns the usual A1 notation.
sheet is an optional text string specifying the sheet. In the usual A1 notation, Calc separates the sheet name with a . character; in the R1C1 notation, Calc separates the sheet name with a ! character.

Example:

ADDRESS(4; 3; 2;; "Sheet2")

returns the text Sheet2.C$4. Note the omitted type parameter.

ADDRESS(4; 3; 4)

returns the text C4.

ADDRESS(4; 3)

returns the text $C$4.

ADDRESS(4; 3; 2; 0; "Sheet2")

returns the text Sheet2!R4C[3].

Issues:

  • Excel represents some cell references differently to Calc, so this function is not always portable. For example ADDRESS(1;1;4;;"Sheet2") returns Sheet2.A1 in Calc; the equivalent in Excel returns Sheet2!A1.
  • The type parameter is implemented from OOo3.0 - see Issue 91020 .
  • When storing a document in the older ODF 1.0/1.1 format, any type parameter in an ADDRESS function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. From OOo 3.0, Calc writes in ODF 1.2 draft format as standard, although this can be changed (Tools → Options → Load/Save → General). A type value of 1 is allocated for any ODF 1.0/1.1 format document opened.


See Also
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_ADDRESS_function&oldid=259974"
Views
Personal tools
Navigation
Tools