Sum of same cell on multiple sheets

[Kamolga]

Hi,

I have this formula that works

=SUM('ETU1:ETU9'!G30)

but I need the 9 to be dynamic. Its value is in cell F19 of the same sheet.

I therefore try to get

[LEFT][CODE][COLOR=#222222][FONT=Verdana]=SUM(indirect("'ETU1:ETU"&F19&"'!G30"))[/FONT][/COLOR]

but that does not work. Can you spot what is wrong?[/LEFT]

 

[Special-K99]

Re: Pls help with Formula: Sum of same cell on multiple sheets

INDIRECT doesn't work with a 3D Reference

INDIRECT won't accept a 3D sheet reference. Default solution is using sheets named "start" and "end" which are empty and does not be changed or deleted at all. Then a =SUM(start:end!A1) will sum all values A1 of all sheets placed between the sheets named "start" and "end"

is one solution.

Another solution here

https://www.mrexcel.com/forum/excel-questions/666334-3d-sum-indirect-problem.html

 

[James006]

Hello,

You are right, Indirect() cannot deal with a 3D reference ...

Take a look a t following link:

https://www.mrexcel.com/forum/excel-questions/666334-3d-sum-indirect-problem-2.html

 

[Kamolga]

Re: Pls help with Formula: Sum of same cell on multiple sheets

Default solution is using sheets named "start" and "end" which are empty and does not be changed or deleted at all. Then a =SUM(start:end!A1) will sum all values A1 of all sheets placed between the sheets named "start" and "end"is one solution.

Thank you. At first I did not like that much the option as the file is a template populating pages with variables already and I feared having to go through my codes to hide and unhide the right pages. But then I simply changed my copying macro to

For i = 1 To Range("RngPages").Value - 1
ShETU.Copy before:=ShStop
        ActiveSheet.Name = "ETU" & i
Next i

and my ShStart and ShStop stay very hidden all the time, so I can completely forget about them So sum start-end array works perfectly, even when delete pages
Thanks again for the input, I did search for long and that would have never come to my mind.