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?

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?

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?

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?

"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