Using the INDIRECT function across multiple worksheets

H

halibut

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$13,$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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Code and a sort problem 1
CodeName assignment for the new Worksheet. 4
Use of Indirect function in Data Validation List 3
Excel Macro to Convert indirect function to direct links 2
LABKHAND: Problem using INDIRECT function. 2
Multisheet range in SUMPRODUCT? 5
Using INDIRECT Function and INDEX Function 1
SumIf across multiple worksheets 2

Top