[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=83746

Eike Rathke  changed:

   What|Removed |Added

 Status|NEW |ASSIGNED
   Assignee|libreoffice-b...@lists.free |er...@redhat.com
   |desktop.org |

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=83746

Eike Rathke  changed:

   What|Removed |Added

 CC||da...@seznam.cz

--- Comment #12 from Eike Rathke  ---
*** Bug 93878 has been marked as a duplicate of this bug. ***

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=83746

Eike Rathke  changed:

   What|Removed |Added

   See Also||https://bugs.documentfounda
   ||tion.org/show_bug.cgi?id=93
   ||878

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=83746

Eike Rathke  changed:

   What|Removed |Added

 CC||er...@redhat.com

--- Comment #11 from Eike Rathke  ---
We can't do anything about the 65536 row limit of .xls, but the
983040=1048576-65536 looks odd and likely should be handled differently..

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=83746

--- Comment #10 from tommy27  ---
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today,
LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed
bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this
bug report. During that time, it's possible that the bug has been fixed, or the
details of the problem have changed. We'd really appreciate your help in
getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of
LibreOffice (5.0.5 or 5.1.2 https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of
LibreOffice and your operating system, and any changes you see in the bug
behavior

If the bug is NOT present, please set the bug's Status field to
RESOLVED-WORKSFORME and leave a short comment that includes your version of
LibreOffice and Operating System

Please DO NOT

- Update the version field
- Reply via email (please reply directly on the bug tracker)
- Set the bug's Status field to RESOLVED - FIXED (this status has a particular
meaning that is not appropriate in this case)


If you want to do more to help you can test to see if your issue is a
REGRESSION. To do so: 

1. Download and install oldest version of LibreOffice (usually 3.3 unless your
bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug 

3. Leave a comment with your results. 

4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 
4b. If the bug was not present in 3.3 - add "regression" to keyword


Feel free to come ask questions or to say hello in our QA chat:
http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for your help!

-- The LibreOffice QA Team This NEW Message was generated on: 2016-04-16

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=83746

Michael Stahl mst...@redhat.com changed:

   What|Removed |Added

 Attachment #106331|application/octet-stream|application/vnd.ms-excel
  mime type||

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=83746

--- Comment #9 from raal r...@post.cz ---
Format .xls handles only 65 356 rows
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=83746

--- Comment #8 from Matthew Francis fdb...@neosheffield.co.uk ---
Created attachment 106373
  -- https://bugs.freedesktop.org/attachment.cgi?id=106373action=edit
Expanded example (as ODS)

This expanded ODS example contains the formulae
=VLOOKUP(B1,$C$1:$D$1048576,2)
=VLOOKUP(B1,$C$1:$D$983040,2)
=VLOOKUP(B1,$C$1:$D$983039,2)
=VLOOKUP(B1,$C$1:$D$65537,2)
=VLOOKUP(B1,$C$1:$D$65536,2)
=VLOOKUP(B1,$C$1:$D$65535,2)
=VLOOKUP(B1,$C$1:$D$100,2)

After saving as .xls, closing and reloading, the result in OSX/4.3.1.2 is
=VLOOKUP(B1,$C$1:$D$983040,2)
=VLOOKUP(B1,$#REF!$#REF!:$#REF!$#REF!,2)
=VLOOKUP(B1,$#REF!$#REF!:$#REF!$#REF!,2)
=VLOOKUP(B1,$#REF!$#REF!:$#REF!$#REF!,2)
=VLOOKUP(B1,$C$1:$D$983040,2)
=VLOOKUP(B1,$C$1:$D$65535,2)
=VLOOKUP(B1,$C$1:$D$100,2)

Based on this, it looks to me as if the handling of any row range = 65536 is
broken, sometimes resulting in an incorrect range and sometimes a #REF!

The original reporter did also mention something about seeing #REF!, but I
couldn't reproduce that initially - however, having now done so, it looks like
it's still part of the same bug.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=83746

--- Comment #6 from Matthew Francis fdb...@neosheffield.co.uk ---
Created attachment 106331
  -- https://bugs.freedesktop.org/attachment.cgi?id=106331action=edit
Minimal sample .xls

Should contain =VLOOKUP(B1,$C$1:$D$1048576,2) at A1

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=83746

--- Comment #7 from Jay Philips philip...@hotmail.com ---
Created attachment 106334
  -- https://bugs.freedesktop.org/attachment.cgi?id=106334action=edit
sample file using the provided steps

Opening Matthew's sample xls, A1 is '=VLOOKUP(B1,$C$1:$D$983040,2)' in 4.2.6
and 4.3.3. But when i followed his instructions in creating the attached file
in 4.2.6, reopening results in '=VLOOKUP(B1,$#REF!$#REF!:$#REF!$#REF!,2)'.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=83746

Matthew Francis fdb...@neosheffield.co.uk changed:

   What|Removed |Added

 OS|Windows (All)   |All
Summary|EDITING: When totalling |FILEOPEN: When opening an
   |based on a page number  |.xls with a 1-1M VLOOKUP
   |series created with |range, the range is
   |VLOOKUP, the page numbers   |truncated to 1-983040
   |are displayed as REF after  |
   |saving, closing and |
   |reloading   |

--- Comment #4 from Matthew Francis fdb...@neosheffield.co.uk ---
テストデータをありがとうございます。

新しいファイルでVLOOKUPのターゲットを$C$1:$D$1048576にして旧型xlsとして保存みたら、また開くと確かにターゲットが$C$1:D$983040になってしまいました。
（数値的にはこれは1048576-65536 (1M-64K)であることには関係がありそう）

今も#REF!表示は再現できていませんが、以上の問題が原因ならば解決できるはずと思います。

代わりにExcelを使ってこのようなファイルを開いたらちゃんとまだ$D$1048576になっているし、Excelを使ってxlsとして保存して再び開いても問題ないので、これはおそらくLibreOfficeのxlsセーブ機能の問題のようです。

回避策としては、Excelファイルが必要なときにはできればOffice Open XML Spreadsheet
(.xlsx)を使った方が効果的だと思います。


(Summary in English)

When a formula such as =VLOOKUP(B1,$C$1:$D$983040,2) is saved as an old style
.xls (not .xlsx) and reloaded, the target is corrupted to $C$1:$D$983040

Reproduced on OSX 10.9.4/LO 4.3.1.2

Steps to reproduce:
1. In cell A1 of a new spreadsheet, enter the formula
=VLOOKUP(B1,$C$1:$D$1048576,2)
2. Save the file as .xls (Microsoft Excel 97/2000/XP/2003)
3. Close and reopen the file, and check the target range of the VLOOKUP

Excel shows the correct result when opening such a file, and has no problem
saving/reloading it itself, so this is probably an issue in the import filter
for .xls.
The fact that 983040=1048576-65536 is probably relevant.

The obvious workaround is to use an .xslx for file interchange when possible.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 83746] FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040

[bugzilla-daemon]

https://bugs.freedesktop.org/show_bug.cgi?id=83746

--- Comment #5 from wiwiroon-f...@yahoo.co.jp ---
ご返答ありがとうございます。ひとまず回避策ができたため、私の中では喫緊の問題ではなくなりました……申し訳ありません。xls形式で保存していたのも、Excelファイルが必要なのではなく、以前から使っていたデータがxls形式だったから使っていた、というだけでしたので、今後はods形式で作業を行いたいと思います。

お忙しい中ご対応いただき、ありがとうございました。

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs