I have a cell in my worksheet which links to the previous worksheet. i.e I'm
in Nov 08 and the cell looks at a cell in Oct 08 sheet.

Whenever I copy this sheet to create the next month, I have to update the
link to the previous month's sheet. =Oct 08!A4 must be changed to Nov 08!A4.

Can I create a formula which will do =[cell one to the left]!A4?

Thanks

Maria :)

Try
=INDIRECT("'"&TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-31,"mmm yy")&"'!A4")

This will fail in Jan '09, Jan '10 etc. as it does not take care of the year
change. Though it mentions A1 it is not impacted by the value in A1... as
long as the value there is not #ERROR, #N/A etc.

Try to modify it to take care of Year change...

"Ugnz" wrote:

I have a cell in my worksheet which links to the previous worksheet. i.e I'm
in Nov 08 and the cell looks at a cell in Oct 08 sheet.

Whenever I copy this sheet to create the next month, I have to update the
link to the previous month's sheet. =Oct 08!A4 must be changed to Nov 08!A4.

Can I create a formula which will do =[cell one to the left]!A4?

Thanks

Maria :)

If you're willing to use a User Defined Function which ignores sheet names.

Function PrevSheet(rg As Range)
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

=PrevSheet(A4)


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 16:39:01 -0700, Ugnz
wrote:

I have a cell in my worksheet which links to the previous worksheet. i.e I'm
in Nov 08 and the cell looks at a cell in Oct 08 sheet.

Whenever I copy this sheet to create the next month, I have to update the
link to the previous month's sheet. =Oct 08!A4 must be changed to Nov 08!A4.

Can I create a formula which will do =[cell one to the left]!A4?

Thanks

Maria :)