Issue 48496 - Excel import: range address in INDEX function does not work
Summary: Excel import: range address in INDEX function does not work
Status: CLOSED FIXED
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOo 2.0 Beta
Hardware: All Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2005-04-30 15:54 UTC by rpfitzinger
Modified: 2013-08-07 15:13 UTC (History)
2 users (show)

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

Attachments
Attached xls file demonstrates the issue if opened with OOo (15.50 KB, application/vnd.ms-excel)
2005-05-02 17:38 UTC, rpfitzinger 		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 rpfitzinger 2005-04-30 15:54:48 UTC 
A spreadsheet that has been built with MS Excel XP with the following formula:
=IF(INDEX(DATEN!$D$6:DATEN!$R$154,$B11,$A11)<>0,INDEX(DATEN!$D$6:DATEN!$R$154,$B11,$A11),"")

This xls-file, after opened with OOo 1.9.95 translates the formula to the following:
=IF(INDEX($DATEN.$D$6:$DATEN.$R$154;$B11;$A11)<>0;INDEX($DATEN.$D$6:$DATEN.$R$154;$B11;$A11);"")
which results in an error message. 

After replacing "$DATEN" with "DATEN" the error message disappears. So I believe
there is a mistake in the way the xls-file is opened.
Comment 1 flibby05 2005-05-01 19:57:05 UTC 
solid report, confirm
Comment 2 frank 2005-05-01 21:55:29 UTC 
Please attach a document showing this behaviour.
Comment 3 rpfitzinger 2005-05-02 17:38:08 UTC 
Created attachment 25721 [details]
Attached xls file demonstrates the issue if opened with OOo
Comment 4 daniel.rentz 2005-05-02 17:39:52 UTC 
Problem: DATEN!$D$6:DATEN!$R$154 is stored as dynamic range consisting of two
cell addresses, and not as static range address. This confuses Calc, see issue 4904.

I will take this issue to add a workaround in the Excel filter.

To make this work in Excel and current Calc, you can change the address from
DATEN!$D$6:DATEN!$R$154 to DATEN!$D$6:$R$154 (remove the second sheet name) in
Excel.

Removing the $ before DATEN in Calc has only the effect that the formula is
re-interpreted, thus Calc detects a constant range address. The same would
happen, if you change something else in the formula, even something meaningless,
e.g. F2+SPACE+ENTER. The $ sign is to make the sheet address absolute, similar
to $D$6 vs D6. This is a Calc-only feature and does not exist in Excel.
Comment 5 daniel.rentz 2005-05-02 17:42:59 UTC 
started
Comment 6 daniel.rentz 2005-06-20 13:56:02 UTC 
fixed in SRC680/dr37 (OOo 2.0.1)
Comment 7 daniel.rentz 2005-08-12 10:29:09 UTC 
back to QA

re-open issue and reassign to oc@openoffice.org
Comment 8 daniel.rentz 2005-08-12 10:29:25 UTC 
reassign to oc@openoffice.org
Comment 9 daniel.rentz 2005-08-12 10:29:48 UTC 
reset resolution to FIXED
Comment 10 oc 2005-09-08 13:25:54 UTC 
verified in internal build cws_dr37
Comment 11 oc 2005-11-22 11:10:20 UTC 
closed because fix available in OOo2.0m142