Sunday, December 2, 2012

yogi_Compute Sum of Cells With Cell References To Another Sheet


                                          Google Spreadsheet   Post  #906
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 1, 2012
user robashroy said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/SUsQaceYYVU)
sum with reference to cell in another cell 
This is a cell I want to copy between cells
=sum('12.01D'!$L$2:$L$8)

The 12.01D refers to the sheet in the current book.
How can I replace that with a cell reference so I can just enter the 12.01D into the 1st cell of the line and then the other 40 cells to the right will reference the correct sheet?
---
https://docs.google.com/spreadsheet/ccc?key=0Aj1T44S8KSz3dHZ1NHRaX18tTVJSSFU1eDQ1eC1FWkE
H3 is =ArrayFormula(sum(indirect($A$3&"!$M$2:$Q$8")))
I want it to be =ArrayFormula(sum(indirect($A$3&"B3:C3")))Where B3 is cell M2 and C3 is Q8 so it uses the data in A3 (Sheet), B3(cell) and C3(cell) to perform the sum.
------------------------------------------------------------------------------
following is a solution to the problem