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

Ive 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 isnt capitalising the initial letter of the
worksheet name is a clue, but can anyone tell me what Im doing wrong?