Issue 109271 - ADDRESS function: cannot create absolute sheet name
Summary: ADDRESS function: cannot create absolute sheet name
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOO320m12
Hardware: Unknown All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2010-02-15 08:12 UTC by Oliver Brinzing
Modified: 2013-01-29 21:51 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
address funtion absolute sheet name (7.86 KB, application/vnd.sun.xml.calc)
2010-02-15 08:12 UTC, Oliver Brinzing 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description Oliver Brinzing 2010-02-15 08:12:02 UTC 
in oo 1.1.5 it was possible to create absolute addresses, for example:

=ADRESSE(5;1;1;"$'Sheet 1'")

result: $'Sheet 1'.$A$5

but in OO 3.2 result for =ADDRESS(5;1;1;1;"$'Sheet 1'") is:

'$''Sheet 1'''.$A$5

this range can't be used for a named range ...
Comment 1 Oliver Brinzing 2010-02-15 08:12:46 UTC 
Created attachment 67823 [details]
address funtion absolute sheet name
Comment 2 Regina Henschel 2010-02-15 12:53:24 UTC 
Confirm.
In OOo2.4.3 the formula
=ADDRESS(5;1;1;"$'Sheet 1'")
returns
$'Sheet 1'.$A$5

But in OOo3.2.0 it returns
'$''Sheet 1'''.$A$5

You can get the desired address with the formula
="$" & ADDRESS(5;1;1;"Sheet 1")
Comment 3 ooo 2010-02-15 13:22:56 UTC 
The entire 4th parameter is a sheet name, the $ and embedded ' are just part of
the name. We had to change the semantics there because now almost all characters
are allowed in a sheet name and the argument can't be copied as. Behavior to
this regard is now identical with Excel.

One could argue that the 3rd Abs parameter should also have an effect on the
sheet part of the address, in which case the argument should be just "Sheet 1"
to produce $'Sheet 1'. However, this is not what Excel does because it doesn't
know relative sheet names anyway.

I can only imagine one use case where this would be needed: use the resulting
address string with an INDIRECT() as a named expression on different sheets and
not have the sheet reference move along with. In this case we may indeed have
the 3rd Abs parameter need to affect the sheet part as well for interoperability
with Excel.

See Regina's suggestion for how to construct the desired result.