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.