Hi Ian

You're very welcome. Thanks for the feedback, but the thanks are really
due to Biff who posted the original solution.

--
Regards

Roger Govier


"Ian Yates" wrote in message
...
Roger

It works! Many thanks for your help.

Ian


"Roger Govier" wrote:

Hi Ian

From a previous posting by Biff (T Valko)

If you create a list of your sheet names and call it something like
Snames then the following formula will work

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!b2"),1,IN DIRECT("'"&Snames&"'!b6")))

--
Regards

Roger Govier


"Ian Yates" wrote in message
...
I'm using Excel 2003 SP2, and I have a spreadsheet which shows the
monthly
management accounts for a series of cost-centres, one per
worksheet;
the
worksheets are named "Sheet1" through to "Sheet20. Each worksheet
has
five
columns and about a hundred rows, and their layout is identical.

I also have a worksheet that produces an account for the combined
cost
centres, using =SUM(Sheet1:Sheet20!E6) etc. for each cell. So far
so
good.

The problem is that the cost-centres are divided into sub-groups,
and
I want
to produce sub-totals for each sub-group. Cell B2 on each worksheet
contains
the reference for the sub-group that the particular cost centre
belongs to,
ranging from 1 through to 8.

I've used the following formula for sub-group 1, but it produces a
#NAME?
error:

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheet1:sheet20&"'b2 "),1,INDIRECT("'"&sheet1:sheet20&"'!e6")))

I guess the fact that Excel isn't capitalising the initial letter
of
the
worksheet name is a clue, but can anyone tell me what I'm doing
wrong?