Search code examples
excelexcel-indirect

Excel INDIRECT fails when sheet name is C2a or C2b

Here's a weird problem:
I open a blank Excel 2013 workbook and create 4 new tabs named A2a, B2a, C2a and D2a.
I now go back to the Sheet1 and create a small table with the 4 names in cells A3:A6 and use the following forumlas:
B3 gets =INDIRECT(A3&"!F9")
C3 gets =INDIRECT("'"&A3&"'!F9")
Dragging down these formulas, Sheet C2a gives a #REF! error in column B when all other sheets do not return an error...

I'm fine with using Column C formulas as they will cover cases where there is a space in the Sheet Name but still, I'd like to understand why I get this error.

Solution

  • This will happen with not just letter C but with letter R as INDIRECT is designed to handle R1C1 style notations.

    So to inform Excel that it is literal C and R you need to pass arguments like C3 formula.

    Hope this helps!