automatically change the worksheet reference when new sheet added

[Joel]

I maintain inventory on a spreadsheet. I am making a reference to a range of
cells. I add a new worksheet for each new day. I would like the reference
to point to the last day added rather then what I do now which is point to a
tab name then then rename it.

Is there a way that references and external references to can change
dynamically whenver a new sheet is added to the source workbook/

 

[Billy Liddel]

Joel

You will have to add the new sheet to the end of the workbook and use this
UDF. It just return the name of the last sheet in the book.

Function Lastsheet()
ns = Sheets.Count
Lastsheet = Worksheets(ns).Name & "!"
End Function

In a blank cell, say B1 enter the formula
=lastsheet()&"B4:B14"
where B4:B14 is the range you wish to examine.

Then if you want to sum this range use the formula =SUM(INDIRECT(B1))

Hope this helps

Peter Atherton

 

[Joel]

I think our levels of advancement with this product are not equal, yours
being significantly higher than mine.

What is a UDF?
I see where I have to enter the results of the udf, but where do I define
the UDF?
I don't need to sum or perform any function I just have to display it.

 

[Billy Liddel]

Sorry Joel, I usually mention this.

Press ALT + F11 to open the VB editor, choose Insert, Module and copy the
code into the module. Return to the sheet ALT + Q, and you can then enter the
formula in the sheet as mentioned before.

However, there is one thing I forgot to mention. When you insert a new sheet
the formula will not automatically calculate. This is because it is not
referring to the cells in the worksheet but the workbook itself.

Force calculation by Pressing Ctrl + Alt + F9


Regards
Peter

 

[Billy Liddel]

And just entering =lastsheet() returns the name of the last sheet, is that
what you want?

Peter

 

[Joel]

Very clever. That worked, thank you so much for your help.

 

[Billy Liddel]

Your'e welcome Joel

Very clever. That worked, thank you so much for your help.

 

[Damon Stennett]

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

 

[Ashish Mathur]

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

in message news:[email protected]...