Problem description: In a calc document which worked properly before 3.6.0 release (in 3.5.5.3), the following formula for referencing a field on another sheet worked: =Januari!A2 Using the exclamation point is the Excel way. as of 3.6.0 release, the Excel way no longer works. The following formula does work: =Januari.A2 But it's a lot of work changing all reference using ! to using a . (dot). Steps to reproduce: 1. create 2 sheets, one named 'Januari', one with 'bar' as name 2. fill cell A2 on sheet Januari with the string 'foo' 3. place the following formula in cell A1 on sheet 'bar': =Januari!A2 Current behavior: The contents in cell A1 on sheet 'bar' display '#NAME?' Expected behavior: The contents in cell A1 on sheet 'bar' should display 'foo' Platform (if different from the browser): Browser: Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.60 Safari/537.1
Change the formula syntax to Excel A1 and you should be fine. It looks like your user profile has been deleted.
(In reply to comment #1) > Change the formula syntax to Excel A1 and you should be fine. It looks like > your user profile has been deleted. Could you explain this? Since I mention that the Excel way of referencing (using the exclamation mark) doesn't work anymore. And furthermore, having to change all existing formulas isn't user friendly: - one may have used sheetname!cell on many places, some not known anymore - upgrading to 3.6 in an office is a no-go; one cannot be sure if the opened sheet shows correct values
(In reply to comment #1) > Change the formula syntax to Excel A1 and you should be fine. It looks like > your user profile has been deleted. What do you mean by 'It looks like your user profile has been deleted.'?
Using the Excel delimiters is only supported when you choose ExcelA1 as formula grammar in Extras->Options->Libreoffice Calc->Formula otherwise the default is Calc A1 which of course does not support Excel delimiters.
(In reply to comment #4) > Using the Excel delimiters is only supported when you choose ExcelA1 as formula > grammar in Extras->Options->Libreoffice Calc->Formula otherwise the default is > Calc A1 which of course does not support Excel delimiters. Shouldn't this setting be saved in the document then? If I make a document on my PC with that setting set to 'ExcelA1' anyone opening that document will have a broken document if the setting is set to 'Calc A1'. Either way, I still think this is a bug.
(In reply to comment #5) > (In reply to comment #4) > > Using the Excel delimiters is only supported when you choose ExcelA1 as formula > > grammar in Extras->Options->Libreoffice Calc->Formula otherwise the default is > > Calc A1 which of course does not support Excel delimiters. > > Shouldn't this setting be saved in the document then? If I make a document on > my PC with that setting set to 'ExcelA1' anyone opening that document will have > a broken document if the setting is set to 'Calc A1'. > > Either way, I still think this is a bug. No, the document will be saved with Openformula syntax. The next person getting your file will just open the file and can display it in the formula syntax he has chosen. This is NOT a bug! The formula syntax is a user specific setting and not a document setting.
(In reply to comment #6) > (In reply to comment #5) > > (In reply to comment #4) > > > Using the Excel delimiters is only supported when you choose ExcelA1 as formula > > > grammar in Extras->Options->Libreoffice Calc->Formula otherwise the default is > > > Calc A1 which of course does not support Excel delimiters. > > > > Shouldn't this setting be saved in the document then? If I make a document on > > my PC with that setting set to 'ExcelA1' anyone opening that document will have > > a broken document if the setting is set to 'Calc A1'. > > > > Either way, I still think this is a bug. > > No, the document will be saved with Openformula syntax. The next person getting > your file will just open the file and can display it in the formula syntax he > has chosen. > > This is NOT a bug! The formula syntax is a user specific setting and not a > document setting. Then why was I unable to open the file, natively made in calc and saved in the native file format? You can test it with the file attached to this bug report: https://bugs.freedesktop.org/show_bug.cgi?id=38028
I mean, unable to open it with correct cell references. See sheet 'Invoer', column B. Every cell has a range defined for the drop down referencing the other sheets
I looked up the formula used to built the items for the drop downs: INDIRECT("'"&ALS(A28="-";"";PROPER(TEXT(A28;"mmmm")))&"'!$A$2:$A$30") As you can see, the formula is used to get the range dynamically. I used the ! for referencing the other sheet. My question, should Calc be able to see this formula as referencing another sheet and save the ! as a . (dot)? Or does using 'INDIRECT' make it impossible for calc do detect the ! (exclamation mark)?
(In reply to comment #1) > Change the formula syntax to Excel A1 and you should be fine. It looks like > your user profile has been deleted. Just checked my settings for the formula syntax in LibreOffice 3.5.4.2 Build ID: 350m1(Build:2) it is set to Calc A1. Even with this setting, the INDIRECT function works. So, clearly, something must have changed in 3.6.x . Can someone look into it as this changed functionality can break a sheets values without knowing immediately. Changing the status back to unconfirmed based on this new insight and the question still unanswered in comment #9.
NOT reproducible with Server Installation of "LibreOffice 3.6.2.1 rc German UI/ German Locale [Build-ID: ba822cc] on German WIN7 Home Premium (64bit) and proceeding due to original report. Using syntax matching with syntax of existing formulas always works. This is not a valid bug report. Reporter more or less wrote what he expects what test results might be if a test would be done, not what he saw. Of course using wrong syntax (different from all other formulas in an existing sheet) causes error messages, and none of the comments or the original report contain a hint that the test has been done in the correct way. I still agree with Markus' suspect that reporter did not recognize different formula settings with new version and so failed to create formulas with appropriate syntax. @mondane.woodworker@gmail.com: Your comments are very incomplete, you always present some fragments what are useless without knowing the interrelations. May be hints on <http://wiki.documentfoundation.org/BugReport> will help you to find out what information will be useful to reproduce your problem? Please: - Write a meaningful Summary describing exactly what the problem is - Attach a sample document (not only screenshot) or refer to an existing sample document in an other Bug with a link; to attach a file to this bug report, just click on "Add an attachment" right on this page. - Attach screenshots with comments if you believe that that might explain the problem better than a text comment. Best way is to insert your screenshots into a DRAW document and to add comments that explain what you want to show - Contribute a document related step by step instruction containing every key press and every mouse click how to reproduce your problem (similar to example in Bug 43431) adding a new formula to the sample document. – if possible contribute an instruction how to create a sample document from the scratch - add information -- what EXACTLY is unexpected -- and WHY do you believe it's unexpected (cite Help or Documentation!) -- concerning your PC -- concerning your OS (Version, Distribution, Language) -- concerning your LibO version (with Build ID if it's not a public release) and localization (UI language, Locale setting). I wonder How you did the tests. The original report contains hints that you use WIN, LibO Version in Comment 10 looks like version numbering I am used to see in Ubuntu. –- Libo settings that might be related to your problems -- how you launch LibO and how you opened the sample document –- Whether your problem persists when you renamed your user profile before you launch LibO (please see <https://wiki.documentfoundation.org/UserProfile#User_profile_location>) -- everything else crossing your mind after you read linked texts
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/FDO/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 FDO