Created attachment 38029 [details]
testcase 1

Created attachment 38030 [details]
testcase 1

Created attachment 38031 [details]
testcase 2

This issue appears to be a misunderstanding of the CHOOSE function as used in
Calc. In MS Excel, the CHOOSE function can either return values or cell
references. That is, Excel will choose based on context whether to return the
contents of cell A6 or the referece "A6" (refering to cell A6).

In Calc, the CHOOSE function returns only values.

Let's use the example from your spreadsheet:

SUM($O6:CHOOSE(($H$2-1),$O6,$P6,$Q6,$R6)) where cells O6-R6 all contain the
value "1."

Excel will return a cell reference from the CHOOSE function based on what the
SUM function needs, resulting in (if $H$2-1 evaluates to 2, for example)
SUM($O6:$P6) after CHOOSE has been evaluated. This is a logical function, and
therefore works.

Calc will return the value of the cell called, however. So (if $H$2-1 evaluates
to 2, for example) the same function will result in SUM($O6:1) after CHOOSE has
been evaluated. This isn't a logical function and results in the error.

I established this difference in behavior by reading the Help files in Excel and
Calc on the CHOOSE function and by doing tests to verify the problem. CHOOSE
function works as described in Help for both programs, but Calc's functionality
is a subset only of Excel's functionality in this case.

-----------------------------------

* Changing type to Enhancement request. Requested enhancement: CHOOSE function
should choose based on context whether a cell value or reference is to be
returned, as this will grant more Excel compatibility.
* Lowering priority to P3.
* Adding ms_interoperability keyword.
* Changed summary from "Calc returns #VALUE, Excel calculates formula just fine"
to "CHOOSE function cannot return a cell reference"
* Changed OS to All (I think this applies regardless of OS)

Thanks,

Steve

as enhancement to requirements for decission finding

Dear Steve,
thanks a lot for your analisis!

Dear developers,
please enhance CHOOSE function to return cell reference too.
I am migrating 40+ users to OOo and this function is used in number of cases. 
What makes it even more critical is it is upper management who uses it.
Thanks a lot.

There may be some confusion here? In Calc CHOOSE *will* return a reference:
=SUM(CHOOSE(1;B4:B5;B5))
works fine

I think the difficulty may be that Calc does not expect a function after the 
colon in a range:
=SUM(b4:indirect("b5"))
gives a #NAME? error, even though INDIRECT always returns a reference. Calc 
seems to be trying to recognise indirect as a named cell.

This seems to be yet something different. With the changes in CWS odff related
to issue 4904 (implementation of range operator) loading the .ods file works
fine now, but loading the .xls files still results in #VALUE! error as if the
import filter doesn't transform that into the correct token sequence.

Furthermore, re-editing the formula in J6 will calculate the value in the cell
but that will not update cell values in K6 and L6, which get updated only if
those formulas are re-edited as well. Seems as if broadcasters/listeners are not
setup correctly. However, that may be an effect related to the original problem
and should be re-investigated once that is solved.

Defect of import filter.

excel import fixed

Reassigning to QA for verification.

THANK YOU very much!!

verified in internal build cws_odff

Hello kpalagin, *,
I have tested the first attached file with DEV300m28 under Debian SID AMD64 and
it works (i.e. the calculation in J6 takes place ... ;) ) So I close this issue.
@kpalagin: Could you check this with m28 under Windows as well? Does it work for
you too? If not, you can reopen this issue immediately ... ;)