issue a warning if a delete cell or page operation causes a reference error

[ferdiga]

Is your feature request related to a problem? Please describe. Deleting a referenced cell (row/column/page) causes an error in the referencing cell. or Division by zero

in big spreadsheets this error may remain undetected until the cell is visited.

it happens usually if another person is modifying the sheet or even the original creator does not remember that the deleted cell is referenced.

Describe the solution you'd like raise an error instead of quietly allow the error to happen

Describe alternatives you've considered no chance except to visually inspect all cells very cumbersome to reconstruct a destroyed sheet, sometimes not really possible

Additional context Add any other context or screenshots about the feature request here.

[Ezinnem]

I can confirm this.

[ferdiga]

sadly no reaction/solution ?

[ferdiga]

@Ezinnem Hi, As the spread sheet is recalculated with every data entry it shouldn't be too difficult to catch the exceptions mentioned above. probably a few lines of code would create a unique user experience eliminating these errors

[ferdiga]

I am looking for a Xmas present ... Is nobody aware of the time people have to spent to recover from such errors?

[ferdiga]

especially in a collaborative environment the risk gets high that someone deletes a referenced cell not knowing that it's referenced.

[ferdiga]

I have a hard time to understand that this issue is not addressed. checking the cells to be deleted with delete row/column/sheet operation using "detective - trace dependants" should be fairly fast, compared to the damage done and the time needed to reconstruct a broken Spreadsheet.

[mmeeks]

I have a hard time to understand that this issue is not addressed.

Of course, this new feature could be implemented - and you are very welcome to implement it, we would love to have it included as an option. Part of the reason it is not implemented is that no-one has volunteered to do that =) I spent a little while checking - LibreOffice doesn't do this, Excel doesn't do this, Google Sheets doesn't do this (that I saw)

If you'd like to hack on it - would be happy to give you some code pointers - to start with getting the existing dependent tracing code enabled and prettified would probably be most useful and easy.

[ferdiga]

It's a drama, all these spread sheets claim to be office grade. It's like a control lamp in a car which goes on after the event instead of going on avoiding it.

But sorry I am 72 and not going to learn a new language.

I can not read the code, but I would check for the ["#REF!"] in the cell, as apparently all dependent cells are calculated if a source changes. It should be something minor like

if cell content is "#REF!" 
then popup message "ancestor cell xx of cell yy is deleted and continuing will destroy the logic of the spreadsheet
we suggest to revert this operation
do you want to revert y/n"
if y - revert operation

Same goes for "#DIV/0!" warning, whereas this does not necessarily means a wrong model, but just poor programming not catching the 0 condition.

[ferdiga]

I have a hard time to understand that this issue is not addressed.

Of course, this new feature could be implemented - and you are very welcome to implement it, we would love to have it included as an option. Part of the reason it is not implemented is that no-one has volunteered to do that =) I spent a little while checking - LibreOffice doesn't do this, Excel doesn't do this, Google Sheets doesn't do this (that I saw)

If you'd like to hack on it - would be happy to give you some code pointers - to start with getting the existing dependent tracing code enabled and prettified would probably be most useful and easy.

I have investigated a bit and found one file

https://github.com/LibreOffice/core/blob/129e69f005d4752bb5ec10a8c12d842037394822/sc/source/filter/excel/xelink.cxx#L916

lines where it seems that the #Ref! string ist written into the formula

void XclExpExtNameAddIn::WriteAddData( XclExpStream& rStrm ) { // write a #REF! error formula rStrm << sal_uInt16( 2 ) << EXC_TOKID_ERR << EXC_ERR_REF; }

and here a popup should be raised asking if the user want's to proceed or roll back.

the other file is

https://github.com/LibreOffice/core/blob/212ffc410aeac602efb2c2bfca3eb2d14a31f490/sc/source/core/tool/compiler.cxx#L3248

where many #REF! errors are addressed.

would be great if that could lead to an easy solution

[mmeeks]

Thanks - the first link is an excel export filter =) so not the problem. The second is probably closer - but it seems unlikely that we re-parse the formula from strings each time a cell is moved or deleted. I would expect there is a re-writing function around in sc/source/core/* somewhere to hunt for. Thanks for looking.

[ferdiga]

may be here

https://github.com/LibreOffice/core/blob/78ff4793f9ad45d84f923686095acb670e7a3ea0/sc/source/core/tool/interpr2.cxx#L2629

line 2629

ValidateRef( *pRes); // set #REF! if needed

multiple PushErrors found, but not for #REF!

PushError( FormulaError::UnknownStackVariable);

[mmeeks]

1st one is in a function called ScUnionFunc - I imagine that is some Union spreadsheet function. In general ScInterpreter::ScFoo is an =FOO() function in the sheet - so that's not what you want.

What you want is the code that re-writes other formulae and updates the dependency graph when something is deleted; I would be inclined to read through from 'sc/inc/doc.hxx' for a method that deletes something - and then follow the set of functions it calls through the code - until you find something likely sounding there.

[ferdiga]

another try:

@param pErrRef pointer to "#REF!" string if to be accepted in file L 578 und 593 - May be this should not be accepted, but throw an error.

https://github.com/LibreOffice/core/blob/93cd479aef9b12bc277c76a53a8d6f5804b1db6c/sc/inc/address.hxx#L578

and 6 references to #REF!

https://github.com/LibreOffice/core/blob/93cd479aef9b12bc277c76a53a8d6f5804b1db6c/sc/source/core/tool/address.cxx#L304

[mmeeks]

Best to read the code as I outline above; starting with a function that you know does what you want to chase down; so I would start from sc/inc/document.hxx - and follow a method like "DeleteCol" - which must go through the code you're interested in. Then follow that down - it will jump from Document -> Table -> Column - and on the way somewhere it will update the formulae references and you can start to get close to the code you care about =)

Interesting that there is a 'CanInsertRow' and 'CanInsertColumn' method that may be fertile places to insert some code to do your check/warning =)

[ferdiga]

Hi I asked Grok to write the code please review https://x.com/i/grok/share/pxsAQW3r8ol2dyWWOlOcuWLaV

[ferdiga]

thoughts for some alternatives https://x.com/i/grok/share/dSyE0KAigwELZa5G9dbzNMRwt

[ferdiga]

a better and faster version https://x.com/i/grok/share/LMdPLcHTT6leoOTOVBNO4cwU1

[mmeeks]

As normal with AI - it is a mix of generic ideas that might work, with halucinations of methods that don't exist and so code that would not compile. It is in fact not incredibly hard to do this - you need to read:

sc/source/core/data/documen7.cxx:void ScDocument::Broadcast( const ScHint& rHint )

And the SvtBroadcaster, or - you could read from:

sc/source/core/data/documen7.cxx:void ScDocument::StartListeningCell( const ScAddress& rAddress, sc/source/core/data/bcaslot.cxx:void ScBroadcastAreaSlotMachine::StartListeningArea(

To see the two different types of dependencies we track :-)

Beyond that - please ensure the code compiles before asking for review - it's a good idea to implement this feature - and I think you can do it, and it'll be interesting to see how having some AI assistance can help you get there more quickly - but the code should at least compile before consuming engineering time :-)

Thanks!