Search code examples
excelerror-handlingexcel-formulaexcel-indirect

ISERROR seems to be ignoring #REF! generated by INDIRECT

I am trying to create a reference to a separate sheet using INDIRECT. I also want to check this for errors, so I preface the thing by using ISERROR.

  • H1 is a date value, formatted as "nn m.d". In this case, 42574 returns Sat 7.23
  • 'Sat 7.23'.D2 equals 100

Let's say there's a tab named "Sat 7.23", and I would like to access cell D2. Using INDIRECT and converting the formatted date to text, I create this formula:

=INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2")

In other words, INDIRECT tells me to make the following reference:

='Sat 7.23'.D2

When the tab exists, this functions perfectly (it returns 100). But... what if the tab doesn't exist? INDIRECT returns #REF!, which is to be expected. So, I throw an ISERROR in front of it:

=ISERROR(INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2")

This returns nothing (or I guess FALSE), even though INDIRECT is generating a #REF! error and therefore should be TRUE. Should it not?

To go further:

=IF(ISERROR(INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2")),0,INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2"))

In this case, ISERROR is always true, so this IF always goes to it's "else" statement. Since the reference is invalid, the whole IF statement returns #REF!

Solution

  • I'm not sure what regional language uses nn to represent Sun - Sat in a format mask but ddd is used in an EN-US system and there is an exclamation mark between the worksheet and the cell address.

    =IFERROR(INDIRECT("'"&TEXT(H1,"ddd m.d")&"'!D2"), 0)

    This will return zero when copied to one cell above (#REF! on H0 as a cell address).