Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Function for merging multiple worksheets | Excel Discussion (Misc queries) | |||
HLookup indirect multiple worksheets | Excel Worksheet Functions | |||
Sum Indirect function through multiple sheets | Excel Discussion (Misc queries) | |||
indirect function in different worksheets, losing my hair! | Excel Discussion (Misc queries) | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |