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?

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?

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?

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?