Captain

If you're willing to go with a user defined function.

Copy/paste this User Defined Function to a general module in your workbook.

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

'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.

When you copy this sheet, the formulas will refer to the prior sheet as long
as the prior sheet is to the left of new sheet.


Gord Dibben Excel MVP

On Thu, 31 Mar 2005 15:05:06 -0800, captain stephen <captain
wrote:

Excel 2003. I produce a weekly cash report. Each new weekly report refers to
several cells on the previous week's sheet. I produce each new sheet by
copying the last one and changing its name. I then have to update the
formulas so that they look at the now previous sheet. Is there some way to
enter the sheet tab name into a formula so that the name of the tab is taken
from another cell.