Issue 20587

Summary:

formula corrupted when opening Excel97 file

Product:

Calc

Reporter:

timdeaton <tim>

Component:

code

Assignee:

Status:

CLOSED DUPLICATE

QA Contact:

issues@sc <issues>

Severity:

Trivial

Priority:

CC:

issues

Version:

OOo 1.1

Target Milestone:

---

Hardware:

OS:

Windows XP

Issue Type:

DEFECT

Latest Confirmation in:

---

Developer Difficulty:

---

Attachments:

Description	Flags
Excel 97 "Sample Budget" file	none

Description timdeaton 2003-10-02 05:25:12 UTC

I am trying to use OpenOffice 1.1.0 with spreadsheets from Excel 97 SR-2.  I am 
using a white-box pc with an Amptron motherboard, an AMD Duron 950mhz 
processor, 1gb ram, 40gb hard disk, and Windows XP Home (with all updates 
applied).

I have a budget cashflow spreadsheet with the following structure:
Rows 1-5: heading info
Rows 6: starting balance for the month 
Rows 7-189: detail as follows:
   col A-B: date
   col C:   check #
   col E:   amount
   col F:   running balance
   col G:   explanation

Column F contains the following formula:
   (row 8): =IF(E8=0,"",F$6+SUM(E$7:OFFSET(F8,0,-1)))
   (row 9): =IF(E9=0,"",F$6+SUM(E$7:OFFSET(F9,0,-1)))
   (etc thru row 189)

When I open this spreadsheet in OpenOffice.org (version 1.1.0)
the formula is corrupted to read as follows:
   (row 8): =IF(E8=0;"";F$6+SUM(#NAME!(E$7;OFFSET(F8;0;-1))))
   (row 9): =IF(E9=0;"";F$6+SUM(#NAME!(E$7;OFFSET(F9;0;-1))))
   (etc thru row 189)

As illustrated, there are 2 problems.
1) An extra function with a bad name is inserted inside the SUM function
2) The colon following E$7 is converted to a semicolon.

(Sometime back I tried out OpenOffice version 1.0.1, and it generated an 
identical problem.)

Comment 1 timdeaton 2003-10-02 05:29:15 UTC

Created attachment 9901 [details]
Excel 97 "Sample Budget" file

Comment 2 daniel.rentz 2003-10-08 12:17:04 UTC

Excel allows you to use dynamic ranges (built from named ranges or
formula results). Calc only supports static cell ranges (with explicit
cell addresses). The E7:OFFSET(...) range is not supported by Calc.
But it is planned to add this feature in OOo 2.0.

*** This issue has been marked as a duplicate of 4904 ***

Comment 3 daniel.rentz 2003-10-08 12:17:17 UTC

double->closed