Using the INDIRECT function across multiple worksheets
 

I am trying to use the INDIRECT function across multiple worksheets in
the same workbook and getting an #REF! error.

My intention is to aggregate rows in multiple sheets with an index
variable (total of 12 cells in the rows with a value of 1 to 12 in
cell $A$2 determining how many cells in the rows are aggregated).

My original attempt which resulted in an error was as follow:

=-SUM(sheet1:sheet4!$E$11:INDEX(sheet1:sheet4!$E$13: $P$13,$A$2))


My second attempt (also resulted in an error) was to build the formula
using the INDIRECT function

=SUM(INDIRECT("'sheet1:sheet4'!$E
$13:"&MID(CELL("address",INDEX('sheet1'!$E$13:$P$1 3,$A
$2)),FIND("!",CELL("address",INDEX('sheet1'!$E$13: $P$13,$A$2)))
+1,200)))

It appears that the INDIRECT function does not work when evaluating
ranges that cover multiple worksheets.

=SUM(INDIRECT("sheet1!G27:I27")) works OK


=SUM(INDIRECT("sheet1:sheet4!G27:I27")) produces #REF! error.


Is there a way around this error or can anyone propose a better
solution to my problem?