Hi there,
With a multi-sheet document, is it possible to set a prominent cell to provide an indication that an error condition has occurred Somewhere - Anywhere? preferably within the first sheet of the document but an indicator at the head of each sheet would suffice.
I mean something like an attention-grabbing conditional format that would prompt me to search but obviously something identifying the specific condition/sheet/cell would be even more informative.
You mean that you want to know where in your multi-page spreadsheet suddenly appeared the cell with #DIV/0! or #N/A or Err:511 or something else that cannot be considered the norm? Of course, this can be implemented.
Do you want to be notified immediately after an error? You should be prepared for a significant slowdown - Calc will be forced to double-check all available data after each of your actions, it can take a long time.
A simpler solution would be to perform a “check” on request. From time to time, search the entire spreadsheet and get a list of cells in which the value “looks very much like an error.”
Yes, “sudden” appearance is what I hope to check for.
Normal errors are fairly well captured by “live” verification.
Some cell references have “gone astray” when moving data blocks around the sheets and were missed as they’re not currently in focus AND don’t impact the results of what I was doing at that point in time.
I agree “check on request” may be the most expedient as 32000 cells & 174 Formula groups may be quite high for “auto” testing and I only really need to check if I have radically changed something.
Interestingly, it arose by accident this morning after having - a week ago - removed some columns from a lower level sheet and then ran a temporary filter/sort on a selection of the data.
For some reason, this caused a glitch where the cell references remained accurate but the formula integrity of one row inexplicably failed.
Lots of #REF errors on the one line which then sorted to the bottom of the list.
Unsure why I happened to inspect the bottom rows. Karma!
How to fix it?
Give me some time, it seems I already know how to do it.
OK, try this solution - Calc Global Error indication.ods.
This is a test spreadsheet with several errors in different places.
On the Standard toolbar, on the right, there is a separate button for launching a macro:
As a result of the macro, an additional sheet will be created with the name _ErrorList, which will list the errors found (or - if you are lucky! - then the corresponding message about their absence)
Each error described is a hyperlink, Ctrl+click will allow you to quickly go to the right place.
If you are satisfied with this solution, you can transfer the SearchErrors module from this book to your Standard library using LibreOffice Basic Library Organizer and assign its call to any Calc spreadsheet using Tools - Customize (Toolbars or Keyboard)
Works for me - Brilliantly. Thank you
had just a short look, i LIKE! it! 