Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a relative reference formula from one sheet to another. | Excel Discussion (Misc queries) | |||
Referencing cell in another sheet yields null? | Excel Worksheet Functions | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) |