Hi,

I understand that you want to add up all the new sheets which you keep
adding. For that you have to simply insert the new sheet between the first
sheet and the last sheet. Try it - it works.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Damon Stennett" wrote in message ...
This looks like the solution I need as well, but all I am getting is a
#REF! error.

This is the base formula I want and I have tested and it works.
=sum(worksheet1:worksheet5!H30)

What I want to do is have worksheet5 update every time I add a new sheet.
So I used your idea and have successfully created the module in VB and
then put in a cell(E29) this: ="worksheet1:"&lastsheet()&"H30".

This results in E29 being populated with worksheet1:worksheet5!H30. In
another cell I put =sum(indirect(E29)) and I get #REF! I tried naming cell
E29 and using the name in the formula, but got the same result. When I
evaluate the formula it shows that it is evaluating E29 before the
indirect function, so that when it gets to the function it is actually
evaluating as INDIRECT(worksheet1:worksheet5!H30). That shouldn't happen,
should it?

thanks,
Damon