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.
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
(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
This is not for the UX team. No calc formula bug has anything to do with the UX team.
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.