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

From Apache OpenOffice Wiki
Jump to: navigation, search
m
(New 'type' parameter; removal of pasted dev. message on this topic)
Line 5: Line 5:
  
 
=== 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 47: Line 47:
 
</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 58: Line 60:
 
<tt>'''ADDRESS(4; 3)'''</tt>
 
<tt>'''ADDRESS(4; 3)'''</tt>
 
: returns the text <tt>'''$C$4'''</tt>.
 
: returns the text <tt>'''$C$4'''</tt>.
 +
 +
<tt>'''ADDRESS(4; 3; 2; 0; "Sheet2")'''</tt>
 +
: returns the text <tt>'''Sheet2!R4C[3]'''</tt>.
  
 
=== 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 [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
 +
*When storing a document in ODF 1.0/1.1 format, any <tt>'''type'''</tt> parameter in an ADDRESS function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened.
 
*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.
 
*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.
−
 
−
'''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
 
−
2nd parameter was present, an older version of Calc will return an
 
−
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
 
−
the definition given in the OASIS ODFF/OpenFormula specification
 
−
available at
 
−
http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula
 
  
 
{{Documentation/SeeAlso|
 
{{Documentation/SeeAlso|
Line 115: Line 74:
  
 
* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
 
* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
−
 
+
* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
 
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 
[[Category: Documentation/Reference/Calc/Spreadsheet functions]]
 
[[Category: Documentation/Reference/Calc/Spreadsheet functions]]

Revision as of 14:40, 1 August 2010


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 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. A type value of 1 is allocated for any ODF 1.0/1.1 format document opened.
  • The draft ODFF standard says that the default mode (if mode is omitted) should be 4. Calc and Excel both default to 1, so this may be a problem with ODFF.

Template:Documentation/SeeAlso

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_ADDRESS_function&oldid=176974"
Personal tools