How do I summarise data from several workbooks?
 

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?

How do I summarise data from several workbooks?
 

Try this, Bobak:
http://www.officearticles.com/excel/...rk sheets.htm

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

"Bobak" wrote in message
...
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?

How do I summarise data from several workbooks?
 

The worksheets are in different workbooks so I can't use the formula in this
answer

"Anne Troy" wrote:

Try this, Bobak:
http://www.officearticles.com/excel/...rk sheets.htm

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

"Bobak" wrote in message
...
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?

How do I summarise data from several workbooks?
 

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

How do I summarise data from several workbooks?
 

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?

"vezerid" wrote:

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