Using INDIRECT to sum cells in another spreadsheet

Question

In my worksheet titled “Data!” I have the following successful formula:

=SUM(INDIRECT(CHAR(64+MATCH(E3,A3:N3,0))&MATCH($B165,$A$1:$A$900,0)&":"&(CHAR(64+MATCH(E3,A3:N3,0))&MATCH($B165,$A$1:$A$900,0)+$O$2-1)))

Please don't bother with the details, as my problem is related to referencing another spreadsheet, as explained below. But in sum, the above formula works as intended by summing cells E124 to E133. In cell E3 I have the desired column name. In B165 I have the desired year.

I need the formula to be in a different worksheet. So, in my second worksheet I’ve written:

=SUM(INDIRECT("Data!"&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0))&MATCH($O3,Data!A1:A900,0)&":"&"Data!"&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0)&MATCH($O$3,Data!A1:A900,0)))))

However, this brings me a #VALUE error.

When I break the formula into two parts it works.

That is, this works: CHAR(64+MATCH(Data!E3,Data!A3:N3,0))&MATCH($O3,Data!A1:A900,0)

And this works: (CHAR(64+MATCH(Data!E3,Data!A3:N3,0)&MATCH($O$3,Data!A1:A900,0)))

But when I combine the two it breaks down. I suppose that the error has to do with the parts bolded below where I reference my initial worksheet.

=SUM(INDIRECT(**"Data!"**&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0))&MATCH($O3,Data!A1:A900,0)&":"**&"Data!"**&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0)&MATCH($O$3,Data!A1:A900,0))))))

Any guidance would be greatly appreciated!!

Answer 1

UPDATED
i updated my formula, and this works perfect now.
Here is the corrected version of your formula:

=SUM(INDIRECT("Data!"&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0))&MATCH($G3,Data!A1:A900,0)&":"&CHAR(64+MATCH(Data!E3,Data!A3:N3,0))&(MATCH($G$3,Data!A1:A900,0)+Data!$O$2-1))))

i think this is gonna work.

Answer 2

The correct formula is:

=SUM(INDIRECT("Data!"&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0)))
   &MATCH($O3,Data!A1:A‌​900,0)&":"&CHAR(64+MATCH(Data!E3,Data!A3:N3,0))
   &MATCH($O$3,Data!A1:A900,0)+Data!$‌​O$2-1))