Display "REF!" after Counting, Save and RE:Open in fx:VLOOKUP Problem description: *I'm Japanese and well not speak English, then, I write Japanese ,sorry. まず、ページシートにページ番号と型番だけが入力されたリストがあります。次に集計シートのB列には型番があり、C列には金額が入っています。集計シートのA列にはB列の型番をページシートで調べるVLOOKUPが設定してあり、ページシートを探して同じ型番のものがあれば、その横にあるページ番号を表示するようにしています。この状態で、ページを元にして合計の集計を行います。ここまではうまく行っています。 ところが、集計後のページを保存して再度開きなおすと、集計シートのA列が全てREF表示になってしまいます。 =VLOOKUP(C2,$ページ.#REF!$#REF!:#REF!$#REF!,2) こういう式が入ってしまいます。閉じる前までは =VLOOKUP(C2,$ページ.A$1:B$200,2) といった式が入っていました。 Steps to reproduce: 1. VLOOKUPで作ったページ番号列を元に集計を取る 2. 保存して閉じて再び開く 手順で言うとこれしかしていませんが、何度やっても再現します。 日本語では受け付けてもらえないかもしれませんが、大変困っていますので、ダメもとで投げてみたいと思います。よろしくお願いいたします。 Current behavior: Expected behavior: Operating System: Windows 7 Version: 4.2.5.2 release
バグレポートをありがとうございました。 直ちにはバグを再現できませんでした。お手数かけてすみませんが、問題のファイルを添付していただけませんか。機密データが入った場合には、余計なシートや数字を除去した形でも大丈夫ですが、元の状態でのバグが見えたら助かります。 うまく再現できたら英語での再現手順を書かせていただきます。 よろしくお願いします。 (In English) Thank you for the bug report. I couldn't immediately reproduce the bug. Could you possibly attach the problematic file? If it contains confidential data, you can remove all unnecessary sheets and figures, but it would be helpful to see the bug in its original form. Once the bug has been reproduced, I will write reproduction instructions in English. Thank you.
Created attachment 106291 [details] テスト用ダミーデータ
ご返答ありがとうございます。 再現しようとしてしていたときに、ひとつ条件が抜けていたことがわかりました。 「xls形式で保存したとき」限定のようです。 ファイルを添付しましたが、こちらで １：小計を取ってods形式で保存する ２：そのままxls形式で保存する という2パターンでは問題ありませんでしたが、 ３：小計を取ってxls形式で保存する としたときに、再び開きなおすとREF表示になることがわかりました。 さらに、再現させない手順も判明しました。 ダミーデータのリストシートのページ列には現在、 =VLOOKUP(B781,ページ.$A$1:$B$1048576,2) といった数式が入っていますが、 この「$B$1048576」のところの数字を「$B$900」のように小さくすれば、 xls形式で開いても問題なく表示されるようです。 おそらくExcelとCalcを交互に使っていたことが原因のようにも思います。 Excel側のVLOOKUP関数の場合、範囲指定は =VLOOKUP(B781,A:B,2) という書き方になりますが、このA:BがCalcへ移ったときに、 =VLOOKUP(C34,$ページ.A$1:B$983040,2) のように、勝手に数値が入力されていました。 xls形式の保存は保障外だったかもしれませんが、 ひとまず以上を報告させていただきます。 よろしくお願いいたします。
テストデータをありがとうございます。 新しいファイルで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.
ご返答ありがとうございます。ひとまず回避策ができたため、私の中では喫緊の問題ではなくなりました……申し訳ありません。xls形式で保存していたのも、Excelファイルが必要なのではなく、以前から使っていたデータがxls形式だったから使っていた、というだけでしたので、今後はods形式で作業を行いたいと思います。 お忙しい中ご対応いただき、ありがとうございました。
Created attachment 106331 [details] Minimal sample .xls Should contain "=VLOOKUP(B1,$C$1:$D$1048576,2)" at A1
Created attachment 106334 [details] 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)'.
Created attachment 106373 [details] 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.
Format .xls handles only 65 356 rows http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
** 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
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..
*** Bug 93878 has been marked as a duplicate of this bug. ***
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6821ad076c276b997c44520fd700817566a718c5 Resolves: tdf#83746 wrapAddress() didn't do what it was supposed to do It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/25182 for 5-1
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=01a3831f9dc1b9f64775f3382180d27a3b51f131&h=libreoffice-5-1 Resolves: tdf#83746 wrapAddress() didn't do what it was supposed to do It will be available in 5.1.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Commit Notification from comment #13) > Affected users are encouraged to test the fix and report feedback. This may be related to: Bug 99980 FILEOPEN: XLS and XLXS filters with absolute & relative referencing of entire column (and maybe row) Thank you