After editing a formula I was used to simply click away in another cell, and editing changes where saved. Now when I am doing so Calc writes the cell coordinates in the formula and creates an error. I am using Libre office 7.3.0 on Windows.
All versions of Excel and Calc behave the same since 30 years. When you select a cell range or click a single cell while editing a formula, a reference to the clicked selection is inserted in your formula.
Formula input is initialized by a leading = + or -.
You can hit the enter key or the tab key to finish input that started with = + or -
edit: fixed typo = instead of ?
…when entering numbers, text or dates.
Hi, Thank you for your answer, but my problem is not solved. Example: I have the formula =A2 + A4. I am editing to =A2 + A5. When I am clicking away to B6 then the formula shows : = A2 + A5B6. When I am doing now “ENTER” then I have the error #NAME?
As @Villeroy said, use the enter key instead of clicking away - or click the Accept icon just to the left of the input box when entering a formula.
Clicking away from the input box to accept what you have entered only works for values that are not formulae - numbers, text or dates - as mentioned by @LeroyG
Hi, Thank you, your solution is working. I am coming from Excel, and I was used to work in the way to only clicking away, if it is only data, or a formula. I have to change my behaviour.
Hi @Villeroy, thank you for your answer. My problem is the following: I have a formula, for example =A2 + A4. I am editing to = A2 + A5. Then I am clicking away to any cell, for example B6. When I am doing this in Excel, then the edited formula is saved . When I am doing this in Libreoffice, then I have the formula = A2 + A5B6, giving. an error. Since I am coming from Excel, was used to work in the described way for many years, I have to change.
Thanks for the interesting topic. Yes, if another cell is clicked in edit (entry) mode of a cell, Excel and Calc behave differently.
Calc inserts the cell address into the formula.
Excel parses the character that precedes the cursor (?). If it is a letter, number, underscore…, then the input is terminated and an attempt is made to move to another cell. If the formula contains a syntax error when input is terminated, a message will be displayed and input mode will be resumed.
dear @sokol92, exactly, that’s my problem. And now as I was used to work for many years with with Excel I have problems to change my workflow. Do you thin this could be a proposal for the Libreoffice- developer-team?
When editing a formula cell, it is expected that clicking a cell inserts its cell reference at the current cursor position of the formula. It is a requested and useful feature.
You may submit an enhancement request (RFE) at the bug tracker to do that only after an operator or opening parenthesis or function parameter separator.
The developers have just paid attention to your question and you have a chance to realize your dream.
Sounds good! Greetings from Berlin, Germany!
Good luck! I hope it was clear that the developer of Libreoffice is @erAck (somewhere not far from you).