Jump to next cell with error

[pwaring]

I often use software to produce spreadsheets that have tens of thousands of rows and several columns containing formulas. Occasionally a few cells will contain errors, usually because of a problem with the data, e.g. a numeric field used in a calculation contains ‘abc’, and therefore the formula result ends up as ‘#VALUE!’. Is there a menu item or keyboard shortcut that I can use to quickly jump to the next cell with an error? Searching for the error code doesn’t appear to work, and scrolling through trying to manually spot errors isn’t practical if there’s one error in a ten thousand row spreadsheet.

I’m running LibreOffice 6.3.4.2 on Ubuntu.

[anon73440385]

I’d use Data -> AutoFilter and select #VALUE! (I’m not aware of a shortcut you are asking for).

[pwaring]

Unfortunately that filters out all the correct rows, which makes it difficult to work out the source of the error if the formula pulls in data from other rows (usually the previous one).

[anon73440385]

Sigh… I wrote … and select #VALUE!

[Lupp]

Assuming you want to find errors in column C you may use something like
=HYPERLINK("#"&ADDRESS(MATCH(TRUE();ISERROR($C$1:$C$25000);0);COLUMN($C$1)))
and the Ctrl+Click on the resulting text.

[pwaring]

That doesn’t appear to work, it just fills the cell with ‘#$C$1’.

[anon73440385]

@Lupp wrote Ctrl+Click on the resulting text.