Automatically change a worksheet reference when a new worksheet is added

Hi there,

What I am trying to do is have a function update automatically whenever a new worksheet is added to my workbook.

This is the base formula I want. I have tested and it works.

Please Login or Register  to view this content.

Now, what I want to do is have worksheet5 update every time I add a new sheet. Ex. When I add worksheet6 I want the function to update to this:

Please Login or Register  to view this content.

.

I used an idea I found on eggheadcafe, submitted by Peter Atherton. It involves creating a User Defined Function like so:

Please Login or Register  to view this content.

I then put in a cell(E29) this:

Please Login or Register  to view this content.

.

This results in E29 being populated with

Please Login or Register  to view this content.

. In another cell I put

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

. That shouldn't happen, should it?

thanks,
Damon

(Copied from my yesterday post)

Try this...

Insert a new worksheet BEFORE the first sheet.
Name the new sheet: FirstSheet

Insert a new worksheet AFTER the last sheet.
Name the new sheet: LastSheet

Now this formula sums Col_K in all sheets between FirstSheet and LastSheet, inclusive:

Please Login or Register  to view this content.

Now, when you insert a sheet anywhere between FirstSheet and LastSheet,
it will be includeded in the sum.

Does that help?