IFERROR does not catch INDIRECT's missing-sheet #REF! in LibreOffice

IFERROR is the one error handler everybody trusts. Microsoft documents it as returning your fallback for any of the seven error values — #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL! — and Google Sheets documents the same blanket behaviour. In LibreOffice Calc our harness found one case where it does not fire: the #REF! raised by INDIRECT when the reference string names a sheet that does not exist. =IFERROR(INDIRECT("'Nope'!B2"),"missing") returned #REF!, not "missing" — identically in all four builds we ran: 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3.

Executed results

The workbook contains no sheet named Nope. Setup for the INDEX row: A1:A3 = 1, 2, 3.

The Excel column is the documented-expected value recorded in our test corpus, taken from Microsoft's function reference for each function; we did not run Excel. Google Sheets has not been put through the harness, so that column stays honest rather than guessed. The LibreOffice column is what the harness computed by recalculating the workbook in each of those four LibreOffice Calc builds. It is a single column because all four agreed on all ten rows — every value below was returned by 24.2.0.3, by 24.8.7.2, by 25.2.0.3 and by 25.8.7.3. Nine of the ten rows agree with the documented Excel value. One does not, and it is row two.

The last two rows are there to keep the comparison fair. IFNA returning #REF! is not a divergence — IFNA is documented to trap #N/A and nothing else, in every engine, which is exactly why the row below it lets a #DIV/0! through in Excel's documentation and in our run alike. If you have been using IFNA as a general error trap, that is a portable bug rather than a Calc one.

Why it matters

The affected pattern is not exotic. It is the standard way to build a dashboard whose data source is chosen by the reader: put a tab name in a cell, assemble the reference as text, and read it with INDIRECT.

=IFERROR(INDIRECT("'"&A1&"'!B2"),"No such tab")

Month pickers, per-client tabs, per-region tabs, a validation dropdown of sheet names — they all reduce to that formula, and the IFERROR wrapper exists precisely because the tab named in A1 may not exist. In Excel the wrapper does its job. In LibreOffice Calc — in every build we ran, from 24.2.0.3 through 25.8.7.3 — the guarded formula shows #REF! anyway, and it does so at the moment the sheet is genuinely missing — when someone renamed a tab, deleted last quarter's sheet, or typed a name with a trailing space. A grid of these cells goes from "one clean fallback message" to "a wall of #REF!", and anything downstream that sums or charts the column inherits the error.

It also breaks quietly in the other direction during a migration audit: because IFERROR handles every other error source correctly, spot-checking a few formulas will not reveal the gap.

What to use instead

Both replacements below rest on values our harness executed in all four LibreOffice Calc builds (24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3), where they behaved identically, and the ISREF form was additionally executed end-to-end in a real multi-sheet workbook for our how-to page, where it returned the fallback text rather than an error.

Test with ISREF first. This is the most precise fix, because "can this reference be resolved?" is exactly the question being asked:

=IF(ISREF(INDIRECT("'"&A1&"'!B2")),INDIRECT("'"&A1&"'!B2"),"No such tab")

That exact formula returned "No such tab" in the multi-sheet run behind our how-to page, with A1 naming a missing tab; in the corpus run above, ISREF on its own returned FALSE for the unresolvable reference and TRUE for a valid one. The cost is that INDIRECT is written twice and therefore evaluated twice; with a volatile function that recalculates on every edit, it is worth confining the pattern to the handful of cells that actually need it.

Or wrap the test in ISERROR. =ISERROR(INDIRECT("'Nope'!B2")) returned TRUE, so =IF(ISERROR(INDIRECT(ref)),"No such tab",INDIRECT(ref)) follows from that, and it catches other failure modes (malformed strings, a name with a stray character) in the same test. Use ISREF when you want to know specifically that the tab is missing; ISERROR when any failure should produce the same message.

What does not help: swapping IFERROR for IFNA, which traps less, not more; and testing for the error code with =IF(ERROR.TYPE(...)=4,...), which still requires the error to reach a function that will look at it. If the tab list is fixed and short, the structurally better answer is to avoid INDIRECT altogether — a CHOOSE over real references (=CHOOSE(MATCH(A1,{"Q1","Q2","Q3"},0),Q1!B2,Q2!B2,Q3!B2)) keeps live references that follow renames and insertions, which string-built references never do.

Our how-to on referencing a cell on another sheet shows the same divergence executed in a real multi-sheet workbook, alongside the quoting rules for tab names with spaces and the other INDIRECT caveats worth knowing before you build on it.

Honest limits

These cases were executed in four LibreOffice Calc builds — 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3 — through our standard .xlsx recalculation path (formulas written with no cached value, recalculated by LibreOffice, values read back). All four produced identical values for all ten rows, so there is no “since” release to report: this is long-standing behaviour across the range we tested, not something a recent version introduced. What we still cannot say is when it began. 24.2.0.3 is the oldest build we have run, so releases older than that are outside the claim, and so is any release between the four we tested — we sampled four points on the timeline, not every build on it.

The Excel and Google Sheets columns are documentation, not execution: Microsoft documents IFERROR as trapping all seven error values including #REF!, and ISREF as returning TRUE when its argument refers to a reference. We did not run either app. Nothing here involves dynamic-array spilling or drawing objects, so the usual spill-range and shape caveats do not apply. One more scope note: the reference string in these cases uses Excel's ! sheet separator, which is what an .xlsx workbook carries and what LibreOffice parses in a document that arrived in that format; a native .ods set to Calc A1 syntax uses a dot, and we did not execute that variant.

Check before you migrate