How do I summarise data from several workbooks?

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?
 
G

Guest

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...
 
V

vezerid

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
 
G

Guest

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Merge Multiple workbook sheets into one workbook 0
summarizing data from various workbooks 3
Summarise data from several worksheets in a workbook 2
Linking data within several Workbooks 1
Link future workbooks to a summary workbook... 1
Referencing Data in Closed Workbooks 4
Dynamic named range reference from external workbook 2
Multiple workbooks to one worksheet 3

Top