Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when new sheet added
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/ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when new sheet added
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" wrote: 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/ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when new sheet ad
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" wrote: 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" wrote: 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/ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when new sheet ad
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 "Joel" wrote: 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" wrote: 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" wrote: 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/ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when new sheet ad
And just entering =lastsheet() returns the name of the last sheet, is that
what you want? Peter "Joel" wrote: 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" wrote: 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" wrote: 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/ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when new sheet ad
Very clever. That worked, thank you so much for your help.
"Billy Liddel" wrote: 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" wrote: 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/ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when new sheet ad
Your'e welcome Joel
"Joel" wrote: Very clever. That worked, thank you so much for your help. "Billy Liddel" wrote: 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" wrote: 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/ |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when newsheet ad
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change the worksheet reference when new sheet ad
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change a reference when the sheet change the folder? | Excel Worksheet Functions | |||
Automatically change cell reference? | Excel Discussion (Misc queries) | |||
Automatically change reference cell each month | Excel Discussion (Misc queries) | |||
Automatically change tab reference | Excel Discussion (Misc queries) | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions |