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.
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!
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).