Summing up data from various worksheets

[mark_vi_]

I have daily data all on different worksheets (31 in total) and I would
like to add up all of the data in these worksheets in a monthly
compilation

I have so far been using the =sum('jan. 1'!B6, 'jan. 2'!B6 ... etc.)
which although is effective is very time consuming

is there an easier way to sum up all of the b6 cells without entering
them individually?

Thanks

 

[Anne Troy]

Mark, try this:
http://www.officearticles.com/excel/sum_the_same_cell_in_multiple_microsoft_excel_worksheets.htm
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Dave Peterson]

Add two worksheets--one to the far right of all of the worksheets to be summed
and one to the far left of worksheets to be summed.

Then put your summary worksheet outside that "sandwich" of sheets.

Call one worksheet start and the other End.

Then on the summary sheet:

=sum(start:end!b6)

 

[aaron.kempf]

you're guilty of misusing Microsoft Excel. Excel is not a
mini-datawarehouse-- Access is.

start usign Access; it is a lot more powerful for this type of thing

-Aaron

 

[Anne Troy]

I have to disagree, Aaron. There's lots of companies out there not running
the Pro version of MS Office. Summing multiple sheets has been going on for
many years and this is far from misusing Excel. And I'm a real proponent of
"use the right freaking software for the job". Unfortunately, it would take
most people an hour what it takes them a minute to do in Excel. And,
unfortunately, you can't tell their boss's that it's often worth the price
and time.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Harlan Grove]

you're guilty of misusing Microsoft Excel. Excel is not a
mini-datawarehouse-- Access is.

....

Odd. Using a documented feature is misuse!?

Summing across worksheets has been in Excel since 1993 (version 4), major
DOS spreadsheets since 1989 (Lotus 123 Release 3), less well-known
spreadsheets since the mid 1980s. Implicit aggregation across workbook files
(Data > Consolidate in recent versions) has been in Excel since the
beginning.

You have an irrational antipathy towards Excel, and irrationality may make
arguments longer, but it doesn't convince anyone.

 

[aaron.kempf]

Excel is a disease.

Stop making the same 'report' week in and week out-- develop a real
answer to your needs..

I reccomend that you stop using Excel for everything and start using
Access and/or SQL Server

 

[Anne Troy]

Aaron, I hope you're kidding.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Harlan Grove]

(e-mail address removed) wrote...

Stop making the same 'report' week in and week out-- develop a real
answer to your needs..

I reccomend that you stop using Excel for everything and start using
Access and/or SQL Server

Because you don't understand, your recommendations are worthless.

 

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants. See how
simple it is to consolidate any combination of your organisation's accounts.
(No VBA used)

 

[Gord Dibben]

Mark

With your Grand Total sheet first in workbook.

In a cell enter =SUM('jan. 1:jan. 2'!B6)

Alternative, which most prefer and lends itself to flexibility in naming,
deleting and moving sheets.

Insert a dummy sheet to the right of the Grand Total sheet. Name it Start

Insert a dummy sheet at end of sheets. Name it End.

In Grand Total sheet enter =SUM(Start:End!B6)

When inserting new sheets make sure they are inserted between Start and End
sheets.


Gord Dibben Excel MVP

 

[aaron.kempf]

why would i be kidding?

you guys MAKE THE SAME SPREADSHEET WEEK IN AND WEEK OUT!!

i mean-- doesnt anyone else see the inefficiency of this?

make something database-driven so that you can print reports in a bunch
of different layouts without emailing around large 100mb spreadsheets;
and without having multiple copies of your data

i mean-- excel CAN'T report on data as well as Access can.

 

[greaseman]

Aaron,

Both last year and this year, you are showing you can't think as wel
as a human can