Jim

If you're willing to use a User Defined Function.......


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

Say you have 20 sheets, sheet1 through sheet20...........sheet names don't
matter.

Select second sheet and SHIFT + Click last sheet

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP


On Thu, 4 Jan 2007 17:00:00 -0800, Jim wrote:

How can I write a formula to reference a specific cell in a worksheet
immediately preceeding (or immediately after) the worksheet I'm in?

For instance:
If I'm in a worksheet called Week 11 I want my formula to reference a cell
in a worksheet called Week 10. Now I write the formula like this: =H29+'Week
10'!H31. However, I have a bunch of these formulas and a bunch of these
worksheets. Every week I'm in a new sheet, but I always have to look at cell
H31 from the prior week. For now when I generate a new worksheet I copy an
existing one then manually change each formula with the new sheet reference.
Tedious. I'd like to get away from that.

Is there a way for the formula to look something like this: =H29+'Previous
Worksheet'!H31 ?? That way I won't have to manually change it.