How to easily put a sum of all pages
 

I have a multi-worksheet workbook and on the last sheet I want the sum of
certain cells from every worksheet. I.E. on the last sheet I may want in
cell D3 the sum of cells D3 from all the other sheets. I have been doing for
example =Worksheet1!D3+Worksheet2!D3 and so on, but that is cumbersome and if
I insert a new worksheet I have to manually add it to the formula. Is there
a quicker way?

What if the cell in the summary page does not match the cells in the other
worksheets, like I want the sum of all D3's in cell F5 of the summary?

How to easily put a sum of all pages
 

Here's one option:

Insert 2 new blank worksheets into your workbook. Name the first one
"First" and the second one "Last" (or whatever names you want). Move the
"First" worksheet to just before the first sheet you want to sum. Move the
"Last" worksheet to just after the last sheet you want to sum. Then hide
those sheets.

Then use this formula in any cell of your Summary Sheet (which should not be
located between the "First" and "Last" sheets)

=SUM(First:Last!D3)

This formula will now sum all of the D3 cells on every sheet located between
"First" and "Last". So, if you add or remove sheets, the formula will not
need to change, as long as any new sheets are added in between the "First"
and "Last" sheets.

HTH,
Elkar


"bjscheel" wrote:

I have a multi-worksheet workbook and on the last sheet I want the sum of
certain cells from every worksheet. I.E. on the last sheet I may want in
cell D3 the sum of cells D3 from all the other sheets. I have been doing for
example =Worksheet1!D3+Worksheet2!D3 and so on, but that is cumbersome and if
I insert a new worksheet I have to manually add it to the formula. Is there
a quicker way?

What if the cell in the summary page does not match the cells in the other
worksheets, like I want the sum of all D3's in cell F5 of the summary?

How to easily put a sum of all pages
 

Assuming you are summing 10 sheets

=SUM(Sheet1:Sheet10!D3)


"bjscheel" wrote:

I have a multi-worksheet workbook and on the last sheet I want the sum of
certain cells from every worksheet. I.E. on the last sheet I may want in
cell D3 the sum of cells D3 from all the other sheets. I have been doing for
example =Worksheet1!D3+Worksheet2!D3 and so on, but that is cumbersome and if
I insert a new worksheet I have to manually add it to the formula. Is there
a quicker way?

What if the cell in the summary page does not match the cells in the other
worksheets, like I want the sum of all D3's in cell F5 of the summary?

How to easily put a sum of all pages
 

=SUM('Workshsheet1:Workshee t7'!D3)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"bjscheel" wrote in message
...
I have a multi-worksheet workbook and on the last sheet I want the sum of
certain cells from every worksheet. I.E. on the last sheet I may want in
cell D3 the sum of cells D3 from all the other sheets. I have been doing
for
example =Worksheet1!D3+Worksheet2!D3 and so on, but that is cumbersome and
if
I insert a new worksheet I have to manually add it to the formula. Is
there
a quicker way?

What if the cell in the summary page does not match the cells in the other
worksheets, like I want the sum of all D3's in cell F5 of the summary?

How to easily put a sum of all pages
 

Thanks for all the fast responses!!!

"Bob Phillips" wrote:

=SUM('Workshsheet1:Workshee t7'!D3)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"bjscheel" wrote in message
...
I have a multi-worksheet workbook and on the last sheet I want the sum of
certain cells from every worksheet. I.E. on the last sheet I may want in
cell D3 the sum of cells D3 from all the other sheets. I have been doing
for
example =Worksheet1!D3+Worksheet2!D3 and so on, but that is cumbersome and
if
I insert a new worksheet I have to manually add it to the formula. Is
there
a quicker way?

What if the cell in the summary page does not match the cells in the other
worksheets, like I want the sum of all D3's in cell F5 of the summary?