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