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