Sum across workbooks

[teebruin]

I have about 50 copies of the same worksheet, and quantities were added to each worksheet. Is there any shortcuts that I can use to sum all the different quantities from the different copies of the same worksheet? Or, will I just have to go in and manually do it? Just wondering.

Thanks in advance.

 

[Aladin Akyurek]

On 2002-09-20 16:27, teebruin wrote:
I have about 50 copies of the same worksheet, and quantities were added to each worksheet. Is there any shortcuts that I can use to sum all the different quantities from the different copies of the same worksheet? Or, will I just have to go in and manually do it? Just wondering.

Thanks in advance.

=SUM(Sheet1:Sheet10!A1:A10)

would sum every A1:A10 range of values in Sheet1, Sheet2,..., and Sheet10.

 

[teebruin]

Thanks Aladin. This board is always so helpful.

 

[teebruin]

I think I misspoke. I have 50 copies of the same workbook. I need to sum the same cells in about 50 separate workbooks. Is there a way to do this similar to the sheets example given above?

 

[Yogi Anand]

On 2002-09-20 17:19, teebruin wrote:
I think I misspoke. I have 50 copies of the same workbook. I need to sum the same cells in about 50 separate workbooks. Is there a way to do this similar to the sheets example given above?

Hi teebruin:

I am not quite sure what you are trying to do ... if you have 50 copies of the same workbook with identical entries in all cells, then the sum of values in certain cells would be 50 times the sum of the relevant cells in the worksheets of one workbook.

However if the cell values in workbooks are different, you may do something like is shown in my worksheet simulation ...

Book3
	G	H	I	J	K
22
23	Workbook	Sheet	StartCell	EndCell	sum
24	[y020920a.xls]	Sheet1!	A1	A10	55
25	[y020920b.xls]	Sheet3!	B1	B10	155
26	[y020920c.xls]	Sheet1!	A1	A10	55
27					265
28
Sheet4

</SPAN>

I hope this helps.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-09-20 19:55

 

[teebruin]

Thanks Yogi. I've been trying to adapt your formula, but no luck yet. This issue pops up from time to time, and I just usually do it manually.

The situation is this:

I sent out a workbook to about 50 different people so they can place orders, then they sent it back to me. Column A on each workbook will have quantities for different items, i.e. A4 is qty for item 1, A5 is qty for item 2 and so forth. I want to create a spreadsheet that will go out and total A4 from all 50 workbooks, and then in another cell sum A5 from all 50 workbooks, and so forth for all items.

I hope this is a clearer picture of my question.

Thanks!

 

[Aladin Akyurek]

On 2002-09-23 11:03, teebruin wrote:
Thanks Yogi. I've been trying to adapt your formula, but no luck yet. This issue pops up from time to time, and I just usually do it manually.

The situation is this:

I sent out a workbook to about 50 different people so they can place orders, then they sent it back to me. Column A on each workbook will have quantities for different items, i.e. A4 is qty for item 1, A5 is qty for item 2 and so forth. I want to create a spreadsheet that will go out and total A4 from all 50 workbooks, and then in another cell sum A5 from all 50 workbooks, and so forth for all items.

I hope this is a clearer picture of my question.

Thanks!

Maybe you can use GetValue:

http://j-walk.com/ss/excel/tips/tip82.htm
This message was edited by Aladin Akyurek on 2002-09-23 12:34