Article updated on December 07, 2007

Problem: How to summarize cells from several sheets?
(All sheets have numeric names 1-5)

Solution: This formula summarizes all values in cell A1 in Sheets named 2 to 4, specified in cell A2 and A3.

Let us break this formula to smaller pieces: =SUM(N(INDIRECT(ROW(INDIRECT(A2&":"&A3))&"!A1"))) + Ctrl + Shift + Enter
This part takes the values in A2 and A3 and creates INDIRECT(2:4)

ROW(INDIRECT(A2&":"&A3)) gives 2, 3 and 4 (matrix)

INDIRECT(ROW(INDIRECT(A2&":"&A3))&"!A1") resulting INDIRECT(2!A1) , INDIRECT(3!A1) and INDIRECT(4!A1)

N(INDIRECT(ROW(INDIRECT(A2&":"&A3))&"!A1")) converting to numbers

=SUM(N(INDIRECT(ROW(INDIRECT(A2&":"&A3))&"!A1"))) summarizes all numbers