How do I summarise data from several workbooks?

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

Guest

I am trying to create a summary workbook in Excel to add together data from
the same cell in 30 indentically formatted workbooks

ie
='[Book 1.xls]Sheet 1'!A1+'[Book 2.xls]Sheet 1'!A1+'[Book 3.xls]Sheet
1'!A1+...

When I link to the workbooks in this way I get an error message as the
formula is too long. Is there any way of adding the same cell from 30
workbooks easily?
 
The worksheets are in different workbooks so I can't use the formula in this
answer

Anne Troy said:
Try this, Bobak:
http://www.officearticles.com/excel/sum_the_same_cell_in_multiple_microsoft_excel_worksheets.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

Bobak said:
I am trying to create a summary workbook in Excel to add together data from
the same cell in 30 indentically formatted workbooks

ie
='[Book 1.xls]Sheet 1'!A1+'[Book 2.xls]Sheet 1'!A1+'[Book 3.xls]Sheet
1'!A1+...

When I link to the workbooks in this way I get an error message as the
formula is too long. Is there any way of adding the same cell from 30
workbooks easily?
Click to expand...
Click to expand...
 
The INDIRECT() function will help you here. Create a table with the
names of the workbooks. Say this table occupies cells K2:K31.

=SUM(INDIRECT("["&OFFSET(K1,ROW(1:30),0)&"]Sheet 1'!A1")

This is an array formula (you need to commit with Shift+Ctrl+Enter). It
also requires that all 30 books are open.

HTH
Kostis Vezerides
 
I think I am almost there

I have now got the formula:

=SUM(INDIRECT("'["&OFFSET(D111,0,1,30,1)&"]Sheet 1'!E6"))

Where the OFFSET part of the formula refers to a list of the 30 workbook
names I want to reference.

When I enter this formula the result is the value in cell E6 Sheet 1 of the
first workbook only. If I enter the formula as a 30 row array the values from
cell E6 in each individual workbook are returned one on each row. However
rather than have the values as a list I want the total of all 30 in a single
cell. Any ideas how to get this to work?
 
Back
Top