Greetings,
I used a "user defined function" to reference a cell on the previous worksheet regardless of the worksheet's name.
The function works fine and updates automatically until I open a new workbook; at which point the original worksheet (with the UDF) reads the cells from the newly opened worksheet. I can correct it by pressing F9, but I don't want to manually correct each time.
I looked through the Mr.Excel database but I could not find a reference to this problem.
Here is the UDF:
Function Prev_sheet(rcell As Range)
Application.Volatile True
Dim i As Integer
i = rcell.Cells(1).Parent.Index
Prev_sheet = Sheets(i - 1).Range(rcell.Address)
End Function
I'm new at this, so is there a way to write the function to be workbook specific?
Thanks - Andrew.
I used a "user defined function" to reference a cell on the previous worksheet regardless of the worksheet's name.
The function works fine and updates automatically until I open a new workbook; at which point the original worksheet (with the UDF) reads the cells from the newly opened worksheet. I can correct it by pressing F9, but I don't want to manually correct each time.
I looked through the Mr.Excel database but I could not find a reference to this problem.
Here is the UDF:
Function Prev_sheet(rcell As Range)
Application.Volatile True
Dim i As Integer
i = rcell.Cells(1).Parent.Index
Prev_sheet = Sheets(i - 1).Range(rcell.Address)
End Function
I'm new at this, so is there a way to write the function to be workbook specific?
Thanks - Andrew.