sum function from different sheets dynamically.

[all4excel]

I WANT to use a sum function from different sheets dynamically.
Lets say i have the sheet names as 2000,2001,2002 till 2010
i want to add cells B2 in all the sheets .
For ex:-
=SUM(2000!B2:2010!B2).
NOW , usually we provide the cell range IN A SUM FUNCTION but i want to have the sheet range keeping the cell address constant..
I am storing the Sheet names- IN A SHEET NAME tOTAL.
2000 C2
2001 C3
2002 C4
2003 C5
2004 C6
2005 C7
I am trying to use the Indirect Function
=SUM(INDIRECT("'"&C2&"'!h2").

[FrankBoston]

You can use the summation form:
=SUM('Sheet1:Sheet10'!B1)

Also consult the documentation under 3D References

[all4excel]

[QUOTE=FrankBoston]You can use the summation form:
=SUM('Sheet1:Sheet10'!B1)

i have the sheet names as follows
2000,2001,2002,2003,2004,2005,2006,2007,2008 etc

I want this to be dynamic like i want the sum from different sheets.
Like i would keep two cells B2 and B3 to enter the starting Sheet number ..
I want the total for the cell C2 from 2000 till 2005..
then i will type 2000 in B2 and 2005 in cell B3.

So i will need to use the Indirect function.
=SUM(INDIRECT("'"&B2&":"&B3&"'!C2")
BUT THIS DOES NOT WORK.

[vane0326]

I want this to be dynamic like i want the sum from different sheets.
Like i would keep two cells B2 and B3 to enter the starting Sheet number ..
I want the total for the cell C2 from 2000 till 2005..
then i will type 2000 in B2 and 2005 in cell B3.

So i will need to use the Indirect function.
=SUM(INDIRECT("'"&B2&":"&B3&"'!C2")
BUT THIS DOES NOT WORK.

Then try...

Go to the menu bar:

Insert > Name > Define

Name: Array

Refers to:

=EVALUATE(EVALUATE("if("&MID(GET.CELL(6,!A3),6,2 55)))

Click Ok

Then input formula in cell A3

=IF(1,Evaluate,"sum('"&B2&":"&B3&"'!C2)")


Hope it helps!

[Teethless mama]

I WANT to use a sum function from different sheets dynamically.
Lets say i have the sheet names as 2000,2001,2002 till 2010
i want to add cells B2 in all the sheets .
For ex:-
=SUM(2000!B2:2010!B2).
NOW , usually we provide the cell range IN A SUM FUNCTION but i want to have the sheet range keeping the cell address constant..
I am storing the Sheet names- IN A SHEET NAME tOTAL.
2000 C2
2001 C3
2002 C4
2003 C5
2004 C6
2005 C7
I am trying to use the Indirect Function
=SUM(INDIRECT("'"&C2&"'!h2").

create two dummy sheets calls "First" & "Last"
The "First" sheet is the far left of the worksheets and "Last" sheet is the far right of the worksheets

=SUM(First:Last!B2)

[all4excel]

like i dont want the First and last always i may want the range between 2000 till 2005 instead of 2000 till 2010.

i am trying to use this formula
[ = SUM(INDIRECT("'"&B2&":"&C2&"'!"&D2) ]

where the Cell B2 contains the first Sheet , cell C2 the second sheet and the cell D2 cell address.

No wplease can anyone make this work ..?

[vane0326]

like i dont want the First and last always i may want the range between 2000 till 2005 instead of 2000 till 2010.

i am trying to use this formula
[ = SUM(INDIRECT("'"&B2&":"&C2&"'!"&D2) ]

where the Cell B2 contains the first Sheet , cell C2 the second sheet and the cell D2 cell address.

No wplease can anyone make this work ..?

Did you try my solution in my previous post? It does what you ask.