Relative reference to a cell on a previous sheet
Hi,
I need a reference to a previous sheet where the cell reference is not absolute - ie automatically updates/changes when I move the information on the reference sheet. Ie:If I have a reference on Sheet "Week 30 2006" to ='Week 29 2006'!C6+1 and I add a row, it becomes ='Week 29 2006'!C7+1. The problem is that if I use any of the UDF I have found on the web, the cell reference doesn't change as I move/add information: eg Function PrevSheet(rg As Range) n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = 0 Exit Function End If PrevSheet = Sheets(Application.Caller.Parent.Index - 1).Range(rg.Address).Value End Function 'and in the worksheet, enter '=PrevSheet(E3) Then PrevSheet(E3) does not become PrevSheet(E4). I've also tried with indirect: Function PrevSheet() On Error GoTo EndFunction Application.Volatile True PrevSheet = Application.Caller.Worksheet.Previous.Name Exit Function EndFunction: With Application.Caller.Parent.Parent.Worksheets PrevSheet = .Item(.Count).Name End With End Function where the worksheet formula becomes =INDIRECT("'"&PrevSheet()&"'!C6") Again - C6 is absolute. Can anyone help me with this? Thank you so much Helen |
All times are GMT +1. The time now is 06:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com