3D summing a named range.

[oscarcounts]

I have a workbook with 7 sheets. sheet 1 is a summary and sheet 2 to sheet 6
are detail. The detail sheets have 6 sections in seven columns. Each section
is summed.At the moment I use a formula like this, =sum(first:lastD5) which
works. However if a row is added in any of the detail sheets the summary
sheet is incorrect. Can I use a 3D system with range names? Or is there a
better way to solve this problem?

 

[Héctor Miguel]

hi, oscar !

I have a workbook with 7 sheets. sheet 1 is a summary and sheet 2 to sheet 6 are detail.
The detail sheets have 6 sections in seven columns.
Each section is summed.At the moment I use a formula like this, =sum(first:lastD5) which works.
However if a row is added in any of the detail sheets the summary sheet is incorrect.
Can I use a 3D system with range names? Or is there a better way to solve this problem?

if you don't mind using indirect, a couple of functions, and "build" an array with sheets-names
aahhh, and create/define names for each cell on every sheet, say "myCell"
(don't forget add the sheet-name while defining the (same) name in the other sheets, i.e. SecondSheet!myCell)

=sumproduct(sumif(indirect({"first";"second";"last"}&"!mycell"),">0"))

build the complete set of sheet-names within the constant array
of write the sheet names in a range and refer to them

you will be able to add/delete rows w/out change the reference to "the named-cells"

hth,
hector.

 

[oscarcounts]

hi, oscar !


if you don't mind using indirect, a couple of functions, and "build" an array with sheets-names
aahhh, and create/define names for each cell on every sheet, say "myCell"
(don't forget add the sheet-name while defining the (same) name in the other sheets, i.e. SecondSheet!myCell)

=sumproduct(sumif(indirect({"first";"second";"last"}&"!mycell"),">0"))

build the complete set of sheet-names within the constant array
of write the sheet names in a range and refer to them

you will be able to add/delete rows w/out change the reference to "the named-cells"

hth,
hector.


Hi hector

Many thanks for the advice and formula. I will let you know how it works out.

Regards