Bug Hunting Session
Bug 73344 - INDIRECT fails with defined names of range union / multiple areas
Summary: INDIRECT fails with defined names of range union / multiple areas
Status: CLOSED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-01-07 07:12 UTC by John Vandenberg
Modified: 2015-06-29 18:21 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description John Vandenberg 2014-01-07 07:12:38 UTC
If a defined name contains multiple ranges, unioned, INDIRECT doesnt produce a ref.  This behaviour also occurs on MS Excel 2007, so perhaps it is desirable to maintain compatibility.

To reproduce

A1: 2
A2: 2
A3: 2
A4: =A1~A3
A5: =AREAS(A1~A3)
A6: =SUM(A1~A3)
A7: =COUNT(A1~A3)

define blah to be "$A$1~$A$3"

B4: =blah
B5: =AREAS(blah)
B6: =SUM(blah)
B7: =COUNT(blah)

C4: =INDIRECT("blah")
C5: =AREAS(INDIRECT("blah"))
C6: =SUM(INDIRECT("blah"))
C7: =COUNT(INDIRECT("blah"))

Expected results:

A4=#VALUE
A5=2
A6=4
A7=2

B4=#VALUE
B5=2
B6=4
B7=2

C4=#VALUE
C5=2
C6=4
C7=2

Actual results (Calc and MS Office 2007):

A4=#VALUE
A5=2
A6=4
A7=2

B4=#VALUE
B5=2
B6=4
B7=2

C4=#REF
C5=#REF
C6=#REF
C7=0

Redefine blah to be "$A$1:$A$2" and the results are as expect.
Comment 1 Buovjaga 2014-11-08 15:01:18 UTC
I confirm the behavior, yes, but this is kind of a hairy thing to change, if it breaks compatibility with Excel!

I'd like some more experienced QA person to comment on this.

Win 7 64-bit Version: 4.4.0.0.alpha2+
Build ID: c989f5e0e11e295b11ffc921b0d105869e037e47
TinderBox: Win-x86@42, Branch:master, Time: 2014-11-07_22:50:48
Comment 2 Robinson Tryon (qubit) 2014-12-22 04:15:35 UTC
(In reply to Beluga from comment #1)
> I confirm the behavior, yes, but this is kind of a hairy thing to change, if
> it breaks compatibility with Excel!

Yes, compatibility with other products is prized highly by a large number of our users. Let's tread carefully before we intentionally break from the pack.

> I'd like some more experienced QA person to comment on this.
> 

I'm not sure if this is a question for a Calc dev or for UX, so I'll toss it into ux-advise and cc a Calc dev for additional input.

Status -> NEW
Comment 3 Markus Mohrhard 2014-12-22 04:38:06 UTC
This is not for the UX team. No calc formula bug has anything to do with the UX team.
Comment 4 Eike Rathke 2015-06-29 18:20:59 UTC
Only references and named ranges are evaluated with INDIRECT, it can not evaluate arbitrary named expressions, it is not an interpreter for spreadsheet formulas. As you rightly noted, also Excel doesn't do this, so won't we.