Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Refreshing a User Defined Function
To all:
On Sheet 1 of my workbook, I have a date in cell A1. In the next sheet, I want the same date as the previous sheet plus seven days. When I copy the latest sheet, I want it to reference the sheet located prior to it rather than referencing the original sheet (Sheet 1). After copying 52 worksheets, I want each of them to reference the sheet located just prior to each of the sheets. I found this User Defined Function posted here on this discussion site and it works great except for one thing. If I change the original date on Sheet 1, the UDF does not automatically update. Is there a way that I could make this happen??? Any help would be greatly appreciated. ChrisA Function PrevSheet(rg As Range) 'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1. n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function |
#2
|
|||
|
|||
Refreshing a User Defined Function
You could select that cell with the formula and hit F2 followed by enter. This
tells excel that the formula changed and it's time to reevaluate it. Alternatively, you could add application.volatile to the top of the UDF: Option Explicit Function PrevSheet(rg As Range) 'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1. Application.volatile dim n as long n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function The problem with these UDFs is that excel only recalculates them when it thinks something changed. If you don't give it info (passed in the function call), then excel won't recalc. By making the function volatile, then excel will recalc whenever it recalculates. So you may want to force a recalculation before you trust the results. ps. If you have this in A2 of Sheet2 =prevsheet(A1) and delete row 1 of sheet2 or insert new rows/columns, then your formula may not be what you want. Since excel will adjust that range. You might want to considering passing the address directly to the function: =prevsheet("A1") using this: Option Explicit Function PrevSheet(addr As String) 'Enter =PrevSheet("B1") on sheet2 and you'll get B1 from sheet1. Application.Volatile Dim n As Long n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(addr).Value End If End Function ChrisA wrote: To all: On Sheet 1 of my workbook, I have a date in cell A1. In the next sheet, I want the same date as the previous sheet plus seven days. When I copy the latest sheet, I want it to reference the sheet located prior to it rather than referencing the original sheet (Sheet 1). After copying 52 worksheets, I want each of them to reference the sheet located just prior to each of the sheets. I found this User Defined Function posted here on this discussion site and it works great except for one thing. If I change the original date on Sheet 1, the UDF does not automatically update. Is there a way that I could make this happen??? Any help would be greatly appreciated. ChrisA Function PrevSheet(rg As Range) 'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1. n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
About User Defined Functions | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Web Services function call and data refreshing | Excel Worksheet Functions | |||
User defined charts- font size too small | Charts and Charting in Excel | |||
how to move user defined function | Excel Worksheet Functions |