if a referenced cell is deleted (delete row/column) the referencing cells show #REF if this cell is on a different table or out of view it will not be recognized by the user IN TIME ! IMO deleting a referenced cell should raise a popup "Warning - referenced cell will be deleted - Continue yes/no" "show referencing cells yes/no" this is a major usability issue
UX plz help.
Hi, any progress on this ? can't be a big issue to pop up a window as in the referencing cell a REF error is shown.
We're replacing our use of the 'ux-advise' component with a keyword: Component -> LibreOffice Add Keyword: needsUXEval [NinjaEdit]
unfortunately still not fixed in 5.2.1.2
may be this issue can still be addressed during the bug hunting session 5.3.0 Beta 2 !!!!!
@Eike, Kohei any opinion on this? Would it be a legitimate alert to warn users of potential for data loss? And would implementing something like this be too much of a nag if always enabled, better to just provide as an configurable alert? And pick a default. Probably no interest as a 5.3 late feature, but seems to have merit for 5.4.
(In reply to Ferdinand from comment #5) > may be this issue can still be addressed during the bug hunting session > 5.3.0 Beta 2 !!!!! The bug hunting session is not for fixing bugs, but for finding them.
sorry guys, data loss is always a major bug
A non optional alert would be unacceptable work break. To not create a new option, could be associated with Menu/Tools/Options/LibreOffice calc/General - Show overwrite warning when pasting data.
pls look at the package manager you can not delete a package with depending packages. you must "force" it and THEN it's your responsibility if you get an inconsistent state.
(In reply to Ferdinand from comment #10) One issue per bug, please.
(In reply to m.a.riosv from comment #9) >... > To not create a new option, could be associated with > Menu/Tools/Options/LibreOffice calc/General - Show overwrite warning when > pasting data. Not sure that checkbox would link the right behavior. The overwrite warning there is: "You are pasting data into cells that already contain data. Do you really want to overwrite the existing data." But here we are deleting cell(s) that have referenced linkages--and should be aware of what happens to values in those referenced cells? Warning should probably be an alert that action would delete cell(s) with active reference(s).
Sure, but I hope text can be modified to explain what it's affected by the option. I'm only think how to avoid a new option, because without an option to disable remark for this matter, for me it would be absolutely unacceptable.
Hi I just want to recall the necessity to handle this problem as it is still not addressed after 2 years in 5.3.3.2. please will this feature be made available in 5.4 as suggested in comment 6?
As this is not set to resolved fix it is not implemented, no. First, we certainly do not want to scan the entire document (or broadcast and collect a new before-deletion hint) whether a formula expression references a to be deleted cell or not for each cell/column/row to be deleted. Second, an expression could also reference a cell/range not only in cell formulas but also in named expressions, validation, conditional formatting, ... We could significantly slow things down with such an approach, so anything like that should be optional not activated by default, which then again doesn't help an inexperienced user either. Plus, implementation would be cumbersome. In short, I'm not a friend of this.
Sorry I do not understand this reasoning in comment 15 the referenced cell shows the error immediately (given automatic calc is on) if in addition to the error in the cell a pop up is triggered with the undo option it would solve the problem without noticeable delay. just my 2c
(In reply to Ferdinand from comment #16) > if in addition to the error in the cell a pop up is triggered with the undo > option it would solve the problem without noticeable delay. Ok, I think you just moved the goalposts in such a way that this could now be the job of your own special extension. Eike, do you agree? Then we could close this and Ferdinand can find someone to develop such an extension for him.
Sorry guys, I am a (python, sql) developer myself and I am aware of the danger of deleting referenced cells - a widely missing feature in all spreadsheets I know. The problems hits less experienced users who should be protected against such "stupid" errors.
(In reply to Ferdinand from comment #18) > Sorry guys, > > I am a (python, sql) developer myself and I am aware of the danger of > deleting referenced cells - a widely missing feature in all spreadsheets I > know. > > The problems hits less experienced users who should be protected against > such "stupid" errors. Then you can create a Python extension for it. If we implemented this in core, then we would get dozens of bug reports complaining about performance worsening on deleting cell contents.
(In reply to Ferdinand from comment #0) > if a referenced cell is deleted (delete row/column) the referencing cells > show #REF > if this cell is on a different table or out of view it will not be > recognized by the user IN TIME ! A user deleting data in a spreadsheet should know what he/she is doing. I expect more situations can be found to 'help' users. I wouldn't start along this path.
There is a general disagreement to your idea. I would appreciate a very unobtrusive method of communication like via statusbar. But since we don't have this we communicate per messagebox or infobar not even a balloon tip, and many other situation would need to inform the user, the proposal is a WONTFIX.
(In reply to Cor Nouws from comment #20) > (In reply to Ferdinand from comment #0) > > if a referenced cell is deleted (delete row/column) the referencing cells > > show #REF > > if this cell is on a different table or out of view it will not be > > recognized by the user IN TIME ! > > A user deleting data in a spreadsheet should know what he/she is doing. > I expect more situations can be found to 'help' users. I wouldn't start > along this path. I a agree partly with "a user deleting data", the problem is often "another user is deleting .. " in general I doubt that the majority - especially beginners - know what they are doing. and even myself. after a few years not working in a specific spreadsheet I can't remember some references and I am surprised to find a REF error in another sheet. the function "trace dependents" is available and could be used. example sheet 1 cell a1=1, cell a2=2, sheet 2, cell a1=+$sheet1.A1+$sheet1.A2 clicking trace dependents in sheet 1 cell a2 creates a blue line with a dot in cell a2 and a square - undefined. at least the user would know that deleting the row/column with cell a2 will create REF Error Again, the performance argument is a weak one as the test needs to be fired only on "delete", especially compared with the time needed to repair the error. Sometimes the repair process is almost impossible, if for example the original users has left the company. Checking all backups (if available) is extremely time consuming.
me again ;-) Version: 6.3.1.2 we do get a warning if we want to past new content into cells with content
we also should get a warning if referenced cells are removed
Created attachment 154163 [details] warning overriding cells with content
Hi Ferdinand, (In reply to Ferdinand from comment #16) > Sorry I do not understand this reasoning in comment 15 > > the referenced cell shows the error immediately (given automatic calc is on) > if in addition to the error in the cell a pop up is triggered with the undo > option it would solve the problem without noticeable delay. That sounds as if it is easier to implement, without the negative effects on performance, and other trouble that @erack explained. But I doubt it really is simpler, because it still would be necessary not only do the changes invoked by a user action (which in done automatically, unless it is turned off) to track if one or more of the changes indeed are invalidated results, that then would trigger the popup. The latter is simple, but the step before.. Eike, if you could be so kind to give your expert input on this.. (In reply to Ferdinand from comment #23/25) > we do get a warning if we want to past new content into cells with content > we also should get a warning if referenced cells are removed That could be a completely different approach? Such as: I delete some content for the first time, then a popup tells me: "are you really sure, you may break formulas .. etc" ? But rather generic, probably panicking inexperienced users. And how to act on editing cell contents? How much I like (or not, as a professional trainer ;) ) the idea to prevent users doing stupid things, I still see no workable solution for this..
Hi, i just checked 6.4 suggested solution: Before deleting a column, row or sheet all cells to be deleted must be checked with "Detective/Trace dependents" and raise an error "Cell x:y on sheet z" depends on "Cell a:b on sheet c" for the first positive. This would create a very unique selling point - at least for a while and save a lot of time and tears. And from an usability point of view, I think it's a trade off between performance and time spent for restoring broken sheets. IMO a very clear decision. and if someone really prefers or needs ref errors due to performance issues, this can be made configurable like "iterative references". sorry to be irksome.
(In reply to Ferdinand from comment #28) > Before deleting a column, row or sheet all cells to be deleted must be > checked with... It still requires to scan the whole document, which is time-consuming. See comment 15. However, you may easily write a macro that does exactly what you need.
*** Bug 148319 has been marked as a duplicate of this bug. ***
Grok 2 suggests The `#REF!` token in LibreOffice's codebase within the `sc/source/core/tool` directory is most directly dealt with in contexts involving formula interpretation and error handling. Here are some specific references: - **interpr3.cxx**: This file contains the core logic for interpreting formulas. Here's where you'll find explicit handling of the `#REF!` token: ```cpp void ScInterpreter::GetDouble() { // ... if ( pCur->GetOpCode() == ocBad ) { // ... if ( pCur->GetOpCode() == ocPush && pCur->GetByte() == ocPush ) { if ( pCur->GetString().equalsAsciiL("#REF!", 5) ) { SetError( errNoRef ); return; } // ... } } // ... } ``` This snippet shows that when the interpreter encounters a token representing `#REF!`, it sets an error code (`errNoRef`), which corresponds to the `#REF!` error. - **token.cxx**: Here, you'll find the definition of token types, but not specifically the `#REF!` string. However, the `ScToken` class and its methods like `GetOpCode()`, `GetString()`, etc., are used to handle tokens, including error tokens: ```cpp ScToken::ScToken( OpCode eOp, sal_uInt8 nByte, const String& rStr ) : nOpCode( eOp ), nByteCode( nByte ), aStr( rStr ) { // Constructor for creating tokens, including those for errors } ``` While this doesn't explicitly show `#REF!`, it's the mechanism through which error tokens like `#REF!` are created and managed. - **tokenarray.cxx**: This file deals with arrays of tokens, which can include error tokens: ```cpp void ScTokenArray::AddToken( const ScToken& rToken ) { // Add a token to the array, which could be an error token pTokenList->push_back( rToken ); } ``` - **cellvalue.cxx**: Although less directly related to token creation, this file deals with setting and interpreting cell values, including error values: ```cpp void ScCellValue::SetError( const FormulaError nError ) { // ... if ( nError == FormulaError::NoRef ) { // Here, #REF! would be implicitly handled as part of error setting } // ... } ``` ### Summary: - The `#REF!` string is explicitly checked in `interpr3.cxx` during formula interpretation. - The actual token creation and management for errors, including `#REF!`, are handled through the `ScToken` class in `token.cxx`. - These tokens are then managed in arrays (`ScTokenArray`) in `tokenarray.cxx`. - The representation of errors, including `#REF!`, in cell values is dealt with in `cellvalue.cxx`. This setup allows LibreOffice to handle `#REF!` errors throughout the process of parsing, interpreting, and displaying formulas and their results.
grok2 The `#REF!` error in spreadsheets, like those in Microsoft Excel or Google Sheets, occurs when a formula refers to a cell that is not valid. This typically happens due to several common reasons: 1. **Deleted Cells**: If a cell referenced in a formula is deleted, the formula will return a `#REF!` error because it can no longer find the referenced cell. 2. **Moved or Cut Cells**: Similarly, if cells are moved or cut, any formulas pointing to those cells will result in `#REF!`. 3. **Invalid Range References**: When a named range or a cell range in a formula is altered or becomes invalid (e.g., a range exceeds the spreadsheet's boundaries), a `#REF!` error can occur. 4. **Pasting Over References**: If you paste data over cells that are referenced in formulas elsewhere, those formulas will show `#REF!` errors. 5. **Copy-Paste Errors**: Sometimes, when copying formulas or data, if the references are not updated correctly, this can lead to `#REF!` errors. 6. **Array Formula Issues**: With array formulas, if the formula expects a certain size or shape of the array and it changes, `#REF!` can appear. **Why Spreadsheets Don't Catch This Automatically:** - **Dynamic Nature**: Spreadsheets are highly dynamic; cells can change frequently, and formulas depend on user actions. Automatically catching and resolving all `#REF!` errors would require the software to predict user intent, which is complex. - **User Control**: Spreadsheets give users control over their data and formulas. Automatically altering or correcting formulas might lead to unintended consequences or loss of data integrity. - **Performance**: Checking for `#REF!` errors on every action would significantly slow down spreadsheet operations, especially in large or complex sheets. - **Feedback Mechanism**: The `#REF!` error itself is a form of feedback, alerting the user that something has gone wrong with their formula or data structure. It prompts for manual review and correction. - **Flexibility**: Users might intentionally use formulas that reference external or dynamic data sources that could temporarily show as `#REF!` errors before being resolved. **What Can Be Done:** - **Error Checking Tools**: Most modern spreadsheet applications have built-in error checking tools that can highlight `#REF!` errors, though they don't automatically fix them. - **Auditing Tools**: Features like "Trace Precedents" or "Trace Dependents" help users see where errors originate, allowing for manual correction. - **Regular Audits**: Best practice includes periodic reviews of formulas and data to ensure no `#REF!` errors have crept in due to changes. - **Use of Named Ranges**: By using named ranges, some `#REF!` errors can be avoided as named ranges adjust when rows or columns are inserted or deleted. - **Protecting Key Cells**: Protecting cells that contain critical formulas can prevent accidental `#REF!` errors. While spreadsheets don't automatically catch and fix `#REF!` errors due to the reasons above, they provide tools and mechanisms for users to manage and resolve these errors themselves.
You may use an AI tool to create your macro, but please don't change the status of this report. Comment 15 still stands as the reasonable take.