Product: Spreadsheet
Type: changed
Title: ADDRESS and INDIRECT support additional parameter for A1/R1C1
notation
Posted by: [EMAIL PROTECTED]
Affected: sc
TaskId: i91020
<http://www.openoffice.org/issues/show_bug.cgi?id=91020>
Effective from: cws odff04
CWS:
<http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300/odff04>
CWS status: new
*Flags*
-------
API/ BASIC [ ]
Configuration [ ]
File format change [x]
Help/ Guide [x]
Performance test [ ]
Translation [ ]
UI relevant [x]
*Description*
-------------
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
Send feedback to [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
