Problem description: I have a xlsx file (without macro). The folder named "_RiservatoAxa_" is protected (without password). Steps to reproduce: 1. just open the file with Calc Current behavior: Funtion CERCA.VERT (translated: FIND.VERT) change from the original: =CERCA.VERT("CodicePerito";_RiservatoAxa_!A1:B210;2;FALSO) to: =CERCA.VERT("CodicePerito";$_RiservatoAxa_.A1:B210;2;0) Consequently the function value is always "0" Expected behavior: Function "text" should not change, so the value returned will be the right one pointed in _RiservatoAxa_ folder Note: if I remove protection from _RiservatoAxa_ folder everything works, but it's a workaround Operating System: Windows 7 Version: 4.0.0.3 release
There is no sample file attached?
Created attachment 79401 [details] example file See the attachment. Error is in every sheet/cell which contain the "find.vert" function. For example You can find it in: sheet: Acqua condotta sintesi cell: B5 Maybe that the problem is connected with the protection of the sheet _RiervatoAxa_. Thanks for support! Corrado
Guess that CERCA.VERT = VLOOKUP, FALSO = FALSE If I'm not wrong, there's a difference between LO & MS Office: - in MSO using FALSE - in LO using FALSE() So that function should be: =CERCA.VERT("CodicePerito";_RiservatoAxa_!A1:B210;2;FALSO()) Please try again using that.
Thanks for reply. I think that the error is in the conversion done by LO. Open the file with Excel 2010 and You will obtain: =CERCA.VERT("CodicePerito";_RiservatoAxa_!A1:B210;2;FALSO) Open the same file with LO and You will find: =CERCA.VERT("CodicePerito";$_RiservatoAxa_.A1:B210;2;0) If You look carefully You will see that: _RiservatoAxa_! becomes: $_RiservatoAxa_. a dollar "$" is added as first char and the end point "." becomes an exclamation mark "!" Thanks, Corrado
Yes indeed there is a difference writing in VLOOKUP function between LO & MSO. About 'Protect Sheet' I think it's another issue. Maybe someone can confirm this issue..
Created attachment 79472 [details] original file
Created attachment 79473 [details] protection removed with excel 2010 same file, but with sheet _RiservatoAXA_ protection removed
(In reply to comment #5) > Yes indeed there is a difference writing in VLOOKUP function between LO & > MSO. About 'Protect Sheet' I think it's another issue. > > Maybe someone can confirm this issue.. Please look at the two files that I have just uploaded. CASE 1: 1) open the file ORIGINAL with Excel 2010 and remove sheet _Riservato AXA_ protection. 2) Save the file in xlsx; 3) re-open it with LO and You will see that everything seems to work fine, even if the function is changed ad above stated; 4) if You try to save it in xlsx and reopen it, everything still seems fine, but cell bords became thicker; CASE 2: Otherwise if You: 1) open the file ORIGINAL with LO and remove sheet _Riservato AXA_ protection. 2) Save the file in xlsx. 2) re-open it with LO and You will see that nothing works. Help!!! :-)
(In reply to comment #8) > (In reply to comment #5) > > Yes indeed there is a difference writing in VLOOKUP function between LO & > > MSO. About 'Protect Sheet' I think it's another issue. > > > > Maybe someone can confirm this issue.. > > Please look at the two files that I have just uploaded. > > CASE 1: > 1) open the file ORIGINAL with Excel 2010 and remove sheet _Riservato AXA_ > protection. > 2) Save the file in xlsx; > 3) re-open it with LO and You will see that everything seems to work fine, > even if the function is changed ad above stated; > 4) if You try to save it in xlsx and reopen it, everything still seems fine, > but cell bords became thicker; > > CASE 2: > Otherwise if You: > 1) open the file ORIGINAL with LO and remove sheet _Riservato AXA_ > protection. > 2) Save the file in xlsx. > 2) re-open it with LO and You will see that nothing works. > > > Help!!! > :-) CASE3: One more thing: if You: 1) open CASE2 file with Excel 2010 2) just save 3) re-open it with LO... magic it works again!
Created attachment 79476 [details] original with protection removed by LO
Created attachment 79477 [details] original with protection removed by LO, then reopen with Excel and saved Works again!
(In reply to comment #8) > > CASE 1: > 1) open the file ORIGINAL with Excel 2010 and remove sheet _Riservato AXA_ > protection. > 2) Save the file in xlsx; > 3) re-open it with LO and You will see that everything seems to work fine, > even if the function is changed ad above stated; > 4) if You try to save it in xlsx and reopen it, everything still seems fine, > but cell bords became thicker; Seems that I often saw that bug reported. You can search & comment on that bug previously reported. > CASE 2: > Otherwise if You: > 1) open the file ORIGINAL with LO and remove sheet _Riservato AXA_ > protection. > 2) Save the file in xlsx. > 2) re-open it with LO and You will see that nothing works. Not reproducible on LO 4.0.3.3 (Win7 32bit). Could you please test again using 4.0.3 release? I found another issue that 'Protect Sheet' removed after saving & reopening that XLSX file. Please confirm if you also find that.
(In reply to comment #12) > (In reply to comment #8) > > > > CASE 1: > > 1) open the file ORIGINAL with Excel 2010 and remove sheet _Riservato AXA_ > > protection. > > 2) Save the file in xlsx; > > 3) re-open it with LO and You will see that everything seems to work fine, > > even if the function is changed ad above stated; > > 4) if You try to save it in xlsx and reopen it, everything still seems fine, > > but cell bords became thicker; > > Seems that I often saw that bug reported. You can search & comment on that > bug previously reported. > > > CASE 2: > > Otherwise if You: > > 1) open the file ORIGINAL with LO and remove sheet _Riservato AXA_ > > protection. > > 2) Save the file in xlsx. > > 2) re-open it with LO and You will see that nothing works. > > Not reproducible on LO 4.0.3.3 (Win7 32bit). Could you please test again > using 4.0.3 release? > > I found another issue that 'Protect Sheet' removed after saving & reopening > that XLSX file. Please confirm if you also find that. CASE 1: please, what is the bug report number? CASE 2: I confirm the procedure that I reported is fully reproducible even on LO 4.0.3.3, I need more info from You.
I'm sorry if not describing clearly. Try to describe this issue(s): CASE 1 (borders become thicker after saving & reopening XLSX produced by MSO) See Bug 58356 , Bug 52578 , maybe related too with Bug 53287 CASE 2 - Open attachment 79472 [details] & follow your instructions, you was right..no change with VLOOKUP function & result on cell B5. I'm sorry, Comment #12 tested using attachment 79473 [details] -> I'm not sure, seems the real problem is difference between VLOOKUP function written with MSO & LO (see Bug 54657) * 'FALSE' written in MSO -> change to '0' in LO * 'FALSE' in MSO = 'FALSE()' in LO @Julien Nabet, maybe you could confirm this issue?
> -> I'm not sure, seems the real problem is difference between VLOOKUP > function written with MSO & LO (see Bug 54657) > * 'FALSE' written in MSO -> change to '0' in LO > * 'FALSE' in MSO = 'FALSE()' in LO But it's ok if 'Protect Sheet' disabled from MSO.
On pc Debian x86-64 with master sources updated today, I gave a try to the first file for the moment. I noticed that if in B5, I put 1 instead of 0, then put back 0, it's ok. So I try to force recalculation with Ctrl/Shift/F9 (see https://help.libreoffice.org/Calc/Recalculate) I'll try tests on other files
I tried the second file then I leafed through the other comments, it seems related to recalculation issue each time. IHMO, the cell borders problem is another issue. Kohei/Markus/Eike: one for you?
(In reply to comment #15) > > -> I'm not sure, seems the real problem is difference between VLOOKUP > > function written with MSO & LO (see Bug 54657) > > * 'FALSE' written in MSO -> change to '0' in LO > > * 'FALSE' in MSO = 'FALSE()' in LO > > But it's ok if 'Protect Sheet' disabled from MSO. Yes, Your last row is right! It's a really behavior, and that is why I think that it's not connected with FALSE. Thanks for help!!!
(In reply to comment #17) > I tried the second file then I leafed through the other comments, it seems > related to recalculation issue each time. > > IHMO, the cell borders problem is another issue. > > Kohei/Markus/Eike: one for you? I can confirm that the borders problem is not collegued. I thin it is the same bug n°53287. Thanks guys!
Corrado: the version is the earliest one known, not the more recent. (see https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Version)
(In reply to comment #20) > Corrado: the version is the earliest one known, not the more recent. (see > https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Version) Sorry! :-(
No problem Corrado, now you'll know :-)
** 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 (4.4.1.2 or later): 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) Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-03-03
** 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
This bug needed a minimal test case and a simple explanation. If I understood it well: open attachment 79401 [details] and see sheet: "Acqua condotta sintesi", cell: B5 which is in MSO: =VLOOKUP("CodicePerito";_RiservatoAxa_!A1:B210;2;FALSE) with value 20445. LO opens it as: =VLOOKUP("CodicePerito";$_RiservatoAxa_.A1:B210;2;0) with value 0 BUT recalculation also shows 20445. Recalculation is F9 on that cell or Shift+Ctrl+F9 to recalculate all formulas in the document. And from LO 4.0 that we get with: "Menu/Tools/Options/LibreOffice calc/Formula - Recalculation on file load" setting Excel 2007 and newer to always recalculate or better prompt user. Default option is to "Never recalculate". Previously LO 3.6 and earlier it was "Always" so that's why this bug is repro from 4.0. So it's not about "Fileopen: protected workbook", it's about "Fileopen: XLSX needs hard recalculation". I close this as NotABug with the intention to open a new one with a general problem about recalculations which are not transparent to the user. Even help page https://help.libreoffice.org/Calc/Recalculate is wrong.