Combination of Sum and Indirect function between sheets done dynamically

[all4excel]

formula for adding between sheets dynamically..
like i have 5 sheets namely - 2000,2001,2002,2004,2005..
i want add the cell B2 from each sheet for which the formula would be
=SUM(2000:2005!B2)
But i want to add to provide the sheet names dynamically like ..
I have put the name of the sheets in cell A2 and cell A3 and the cell-address in cel A3.
= SUM( INDIRECT("'"&A2&":"&A3&"'!"&A4)).

Is this possible.?

[arthurbr]

Did you give it a try?
What happened?

[all4excel]

hey arthur i did try your way it does work but what im doing is slightly different im trying to provide the names of the sheets dynamically and not directly.

the formula should work for the situation.

I have 11 sheets from 2000 till 2010 and 1 sheet to put the total.

[ =SUM(INDIRECT("'"&A2&":"&A3&"'!"&A4)) ]

Where cell A2 may contain the name of the Sheet like 2001
Where cell A2 may contain the name of the Sheet like 2006
Where cell A2 may contain the name of the Sheet like the cell address like cell B2.

[vane0326]

I'm with arthurbr. You should stick to your original thread did you try my solution that I posted? It will do exactly what you want. If not please give some feedback so we could better assist you.


http://www.excelforum.com/showthread.php?t=609659

[all4excel]

I tried your approach but in vain.

I hope u guys have understood what i want .
I'l just attach a Screenshot to explain you guys that this does not work as The function Name Evaluate does not exist.

Let me tell you that i was not able to get back to my original question.
So please look into that now as im not able to work on the option porvided by you.
And alo please provide explanation wherever necessary

[vane0326]

Ok there were 2 things wrong. There was a extra space in the define name formula. Example,

2 55

it should be

255

The reason why, sometimes when pasting the formula into the forum it will leave some spaces between.

Also I posted the wrong formula.

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

it should be

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

That was my fault.


I made a small example workbook below.