I did now test this on a windows machine with OOo 3.1 and it seems that
this problem does not occur there. I have not installed OOo 3.1 on my
Ubuntu desktop yet, so I cannot test this here. However, the difference
in behaviour between OOo 3.0 and 3.1 seems to be that the newer version
understands the sheet1!A1 reference that Excel uses, but the older does
not.
I used the following formula in a cell, which is still broken when
exported to Excel from OO0 3.1 though:
=IF(INDIRECT(CELL("contents",$A8)&".$B$5")=D$1,INDIRECT(CELL("contents",$A8)&".$B$1"),0)
Cell A8 contains the name of the sheet that the value of B5 or B1 should
be looked up from. However, when this sheet is saved as an xls file,
the .$B$5 is not converted to !B$5, probably because OOo does not
understand that the value of CELL("contents",$A8) must be a sheet name
by mere virtue of the syntax. It should however, since there is not
other element that can precede a cell reference except a sheet reference
if my synopsis is correct.
In the OOo 3.1 for Windows test I could do, this is still the case.
Excel does not understand sheet1.$B$5. If I use the reference without
INDIRECT though, all is well. OOo translates sheet1.A1 to sheet1!A1
when saved as xls.
Of course the workaround is to use the reference notation that Excel
uses, since OOo 3.1 seems to understand this now, but this is only a
workaround and a complex sheet like they one I built in OOo takes a lot
of time to manually convert and then OOo 3.0 users cannot use the sheet
again, which creates a new problem.
I trust this desrcibes the problem a bit clearer, but if I need to
provide more detail I'd be glad to.
--
OpenOffice.org 3 does not save sheet reference correctly for Excel format
https://bugs.launchpad.net/bugs/402752
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.
--
ubuntu-bugs mailing list
[email protected]
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
