I
Ian Yates
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?
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?