Created attachment 89482 [details] A spreadsheet to generate a printed diary, with fixed entries listed on a second sheet Version: 4.1.2.3 Build ID: 40b2d7fde7e8d2d7bc5a449dc65df4d08a7dd38 Spreadsheet function LOOKUP works as expected except that empty "looked-up" cells display "30" in the result cell; non-empty entries are copied correctly to the result cells. Any text entry added to the looked-up array clears ALL the unwanted 30s. I wondered whether empty cells were being read as numbers and the result somehow appearing in hex. The look-up array had indeed been formatted as "number". Correcting this to 'text' format made no difference. Adding any text, even one space, anywhere in the "look-up" array, even if then deleted, clears all the 30s. By this means I was able to generate a pdf output and print the file. Without that manoeuvre, the 30s appeared in the pdf and thus the print-out. Is it a clue that the first week of this calendar file does not suffer the error? Saving the "corrected" ods file and re-opening it brings back the unwanted 30s. I attach this file, a 2014 calendar, stripped of the personal info.
Please ignore the line beginning "Is it a clue...". In the submitted file, that group of cells did not (but should) contain the LOOKUP function! Apologies.
Hi Mik. I agree with all your findings. I was able to recreate the problem using your file. Can you advise how you created the doc pls? I had several attempts to recreate this scenario (Windows 7 & Fedora Linux) and every time the lookup worked as expected. IE. blanks display blanks. Is it possible for you to create the doc from scratch and recreate the problem?
LibO 4.1.2.3 on Win7 : Tentative explanation : - the LibO help says ("handling of empty cells") that empty cells were filled with 0 or "" in earlier OOo/LibO versions ; I suppose it was 0 in the present case, - in the "cal" sheet, concerned cells show a format of "Date/31" (only the calendar day-in-month) - applying this format to 0 leads to a value of 30 (with default initial date : day 0 is the 30th of december 1899 ; that's OK) - last supposition : there is no automatic recalculate after a loading. If above considerations are true, Not A Bug, simply loading of a calendar saved with a really older OOo/LibO version. I would just recommend to change the "Date/31" format with a "Text" one.
Created attachment 89586 [details] Freshly-created example. Many thanks to Tim and Dominique for their thoughts on this. The file has been evolving over several years, and was probably first created as an xls file. I will check when I have access to the computer that holds this history, and do some experiments. Interesting observation of Dominique's. Changing the formatting to "text" makes the error show as '0' instead of '30', results which also appear as the current example in the cell formatting box even after the error has been cleared from the result cells by the trick described initially. However, experimenting now (on a laptop running XP Home and LO ver 4.1.3.2) produces the error, appearing as '0' in a text or number-formatted result cell. It's more persistent, too, clearing only if the source cells are left with a space in them. (File attached: try.ods)
What should happen to this now? There have been no further entries for a while. The latest upload, "try.ods", was created afresh on a Windows XP laptop, and exhibits the problem in a much smaller and simpler file. It doesn't, however, appear in the attachment list as I see it on this page. Maybe I've done something wrong on this website.
Created attachment 105433 [details] try 2 Hello, I opened file try.ods in LO 3.4.3 . Result of LOOKUP function is 0 or empty (where data is empty or space). I repaired data ranges in your testing file. I mean this is correct, see help https://help.libreoffice.org/Calc/Spreadsheet_Functions#LOOKUP https://help.libreoffice.org/Calc/Handling_of_Empty_Cells Setting as NEEDINFO. Please test with newer version, set back to UNCONFIRMED if you find the bug, otherwise close the bug. Thank you.
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team
Dear Bug Submitter, Please read this message in its entirety before proceeding. Your bug report is being closed as INVALID due to inactivity and a lack of information which is needed in order to accurately reproduce and confirm the problem. We encourage you to retest your bug against the latest release. If the issue is still present in the latest stable release, we need the following information (please ignore any that you've already provided): a) Provide details of your system including your operating system and the latest version of LibreOffice that you have confirmed the bug to be present b) Provide easy to reproduce steps – the simpler the better c) Provide any test case(s) which will help us confirm the problem d) Provide screenshots of the problem if you think it might help e) Read all comments and provide any requested information Once all of this is done, please set the bug back to UNCONFIRMED and we will attempt to reproduce the issue. Please do not: a) respond via email b) update the version field in the bug or any of the other details on the top section of our bug tracker -- The LibreOffice QA Team This INVALID Message was generated on: 2015-05-06 Warm Regards, QA Team