Total up cells from sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook that contains 18 sheets, with the final sheet showing two Total cells one contains the totals from adding together all the cells C66 and the other all the cells C67 in all the previous 17 sheets. I will be alway's using the same cells as I have made a template. Is there an easier way of adding all the cells without typting every sheet reference and cell

Bobby
 
One way:

=SUM(Sheet1:Sheet17!C66)


However, that depends on sheet *position*, i.e. Sheet1 on the left
and Sheet17 on the right. If the user moves Sheet2 to the left of
Sheet1, Sheet2 will no longer be included in the SUM(). Likewise if
the user deletes Sheet1, your formula will return #REF! errors.

A somewhat more flexible way is to create two blank "wrapper
sheets", say "First" (on the left) and "Last" (on the right). They
can be either visible or hidden. Then your formula becomes
=SUM(First:Last!C66) and the user can rearrange sheets to their
heart's content, as long as the sheets stay "inside the wrapper".
 
Thank you, that worked perfect


----- J.E. McGimpsey wrote: ----

One way

=SUM(Sheet1:Sheet17!C66


However, that depends on sheet *position*, i.e. Sheet1 on the left
and Sheet17 on the right. If the user moves Sheet2 to the left of
Sheet1, Sheet2 will no longer be included in the SUM(). Likewise if
the user deletes Sheet1, your formula will return #REF! errors

A somewhat more flexible way is to create two blank "wrapper
sheets", say "First" (on the left) and "Last" (on the right). They
can be either visible or hidden. Then your formula becomes
=SUM(First:Last!C66) and the user can rearrange sheets to their
heart's content, as long as the sheets stay "inside the wrapper"
 
I would like to be able to only add up the range without including the hidden sheets. How can I do that? Trying to figure that out for the past 40 minutes without success

TIA
-Brando

----- J.E. McGimpsey wrote: ----

One way

=SUM(Sheet1:Sheet17!C66

......
A somewhat more flexible way is to create two blank "wrapper
sheets", say "First" (on the left) and "Last" (on the right). They
can be either visible or hidden......
 
Moving the hidden sheets to the right/left of the group that you want might be
the easiest way.
 
Back
Top