software

I have done
SUM(OFFSET(INDIRECT(quot;Sheetquot;amp;numberamp;quot;!E8quot;)..etceter a) to get a sum from a
specific sheetquot;numberquot;. It works as expected.
Now I want to extend this to get the sum of all sheets from sheetquot;0quot; to
sheetquot;numberquot;. However I try it seems that INDIRECT does not like a quot;:quot; and
gives me #REFERENCE.
Why can't I simply do
SUM(OFFSET(INDIRECT(quot;Sheet0:Sheetquot;amp;numberamp;quot;!E8quot;).. etcetera)
and how should I actually solve this task.

Best regards / Ake

quot;Akequot; gt; wrote...
gt;I have done
gt;SUM(OFFSET(INDIRECT(quot;Sheetquot;amp;numberamp;quot;!E8quot;)..etcete ra) to get a sum from a
gt;specific sheetquot;numberquot;. It works as expected.
gt;Now I want to extend this to get the sum of all sheets from sheetquot;0quot; to
gt;sheetquot;numberquot;. However I try it seems that INDIRECT does not like a quot;:quot; and
gt;gives me #REFERENCE.
gt;Why can't I simply do
gt;SUM(OFFSET(INDIRECT(quot;Sheet0:Sheetquot;amp;numberamp;quot;!E8quot;). .etcetera)
gt;and how should I actually solve this task.

You can't do this because of formula syntax. In Excel, ranges are entirely
contained within single worksheets. 3D references aren't ranges. The OFFSET
function *requires* that its first argument be a range, and it returns #REF!
if it isn't.

As for workarounds, you'd need to show your entire formula.
Thanks Harlan,
A full formula for what I do, that works, is i.e
SUM(OFFSET(INDIRECT(quot;Sheetquot; amp; A6 amp; quot;!E8quot;),0,0,1,4)) where cell A6 contains
the sheet number where the sum is to be fetched from (and E8 is the first
cell of interrest (=to be summed) on that sheet. [Returns the sum of a number
of cells from the specific sheet number specified in cell A6]
What I would have _liked_ to do next is something like
SUM(OFFSET(INDIRECT(quot;Sheet0:Sheetquot; amp; A6 amp;quot;!E8quot;),0,0,1,4)) where cell A6
contains the last sheet number (n) in the sheet sequence quot;sheet0, sheet1,
sheet2,...sheetnquot;, across which I want to do the summation.
Thus, in general terms - for all sheets from 0-n, sum all cells of
interrest, please ;-)

Best regards /Ake

Ake wrote...
gt;A full formula for what I do, that works, is i.e
gt;SUM(OFFSET(INDIRECT(quot;Sheetquot; amp; A6 amp; quot;!E8quot;),0,0,1,4)) where cell A6 contains
gt;the sheet number where the sum is to be fetched from (and E8 is the first
gt;cell of interrest (=to be summed) on that sheet. . . .

If this really is representative, then you could eliminate the OFFSET
call.

SUM(INDIRECT(quot;Sheetquot;amp;A6amp;quot;!E8:H8quot;))

gt;What I would have _liked_ to do next is something like
gt;SUM(OFFSET(INDIRECT(quot;Sheet0:Sheetquot; amp; A6 amp;quot;!E8quot;),0,0,1,4)) where cell A6
gt;contains the last sheet number (n) in the sheet sequence quot;sheet0, sheet1,
gt; sheet2,...sheetnquot;, across which I want to do the summation.
....

=SUMPRODUCT(SUMIF(INDIRECT(quot;'Sheetquot;amp;(ROW(INDIRECT( quot;1:quot;amp;(A6 1)))-1)
amp;quot;'!A1:A3quot;),quot;lt;gt;quot;))Ake wrote...
gt;A full formula for what I do, that works, is i.e
gt;SUM(OFFSET(INDIRECT(quot;Sheetquot; amp; A6 amp; quot;!E8quot;),0,0,1,4)) where cell A6 contains
gt;the sheet number where the sum is to be fetched from (and E8 is the first
gt;cell of interrest (=to be summed) on that sheet. . . .

If this really is representative, then you could eliminate the OFFSET
call.

SUM(INDIRECT(quot;Sheetquot;amp;A6amp;quot;!E8:H8quot;))

gt;What I would have _liked_ to do next is something like
gt;SUM(OFFSET(INDIRECT(quot;Sheet0:Sheetquot; amp; A6 amp;quot;!E8quot;),0,0,1,4)) where cell A6
gt;contains the last sheet number (n) in the sheet sequence quot;sheet0, sheet1,
gt; sheet2,...sheetnquot;, across which I want to do the summation.
....

CORRECTED!

=SUMPRODUCT(SUMIF(INDIRECT(quot;'Sheetquot;amp;(ROW(INDIRECT( quot;1:quot;amp;(A6 1)))-1)
amp;quot;'!E8:H8quot;),quot;lt;gt;quot;))Tnx Harlan, It would have taken me forever to find that function in the
manual ;-)

BTW,

The reason for the quot;..(OFFSET(..quot; was to create a function that was
quot;fill-down/rightquot;-able. With quot;OFFSETquot; I can do this with a supporting column,
and get a quite compact writing.
A pondered to use a quot;ROW()quot; construct to allow for quot;fill-downquot;, and it
works. But if you want to have it working both for quot;fill-downquot; and
quot;fill-rightquot; it gets a little bit cumbersome. So therefore I used the
quot;OFFSETquot; solution. (Is there an even better solution, perhaps?)

Best regards / Ake