Tags: Calc Spreadsheets
last edited: 2023-01-24
Find invalid formulas in a spreadsheet
Calc is equipped with an excellent set of tools for analyzing errors in formulas - Detective. Select the cell with the error and in a few clicks find out the source of the errors, which cells the formula in the selected cell depends on, which cells this error affected...
But imagine the situation when you open a large spreadsheet. Very big. Huge. Many sheets, many cells with data, many formulas. Worse, you are not the author of this monster. Even worse, you have to make changes to this spreadsheet - change something, add something, delete something.
Already now you are not sure that there are no errors here. But finding them just by scrolling on it through the data is very difficult. You are not sure if your fixes will not generate new bugs. And they can appear anywhere ("the spaghetti effect - you drag pasta from one side of the plate, and something move on the other side")
The Bugerra extension is designed to find all errors in a spreadsheet and display them all in one place. (Do you remember Bagheera? He is very fast-paced. This one also tries to be fast)
It's very easy to use. Run the macro from the Tools-Detective-Bugerra menu and get a dialogue. If you are lucky, it will contain only one line - "No errors found".
Otherwise, click on the lines in the list and move to the next cell from the list. Fix the error in the formula and click the Refresh button to reread the errors again.
If you choose the sorting "By Importance", the cells that are referenced by more formulas will move to the beginning of the list. It may well happen that by correcting the topmost error, you simultaneously correct all errors.
If there are a lot of mistakes and you don't want to spend your time correcting them (and this is rightly!), click on the "To Sheet" button. The macro will create a new sheet named _BugErrAList in this spreadsheet listing all errors found. Return the spreadsheet to the author - let him waste the time of his life on this nonsense.
Colin Howard Oct 26, 2021, 2:25 PM (17 months ago)
It seems the comment system can't cope with emojis. Those four question marks are how it translates a thumb-sup.
Still, Happy to give John ten stars
Colin Howard Oct 26, 2021, 2:17 PM (17 months ago)
I remember JohnSUN writing the first macro in response to my query in ASK Libre and that worked just fine. I've just discovered his extension and it's still amazing.
Well done sir????