Relative reference to a cell on a previous sheet

[feirhelen]

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

 

[Dave Peterson]

In the first UDF (called with =PrevSheet(E3)), you're actually passing a
reference to the sheet with the formula. If you insert a new row on that sheet
(between 1 and 3), you'll see that the reference changes.

In the second version (=INDIRECT("'"&PrevSheet()&"'!C6")), you're passing a
string.

In both cases, excel doesn't know anything about the sheet that it should be
"monitoring" for changes.

I'll be surprised if anyone can come up with a nice solution for the both the
previous sheet stuff and inserting/deleting rows/columns on that previous sheet.

I've been surprised before, but this doesn't appear possible to me.

Is there a chance that you have a column of unique keys in that worksheet where
you could use =prevsheet() and maybe index/match? The formula wouldn't adjust,
but you may get the result you want.