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.

Yep:
http://j-walk.com/ss/excel/tips/tip63.htm

If you're new to VBA:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm


"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.

1) This formula returns to name of the worksheet that hold the formula
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
I seem to recall (it is too late to experiment) the file must be saved first
for it to work
Suppose I have that in D8 of the worksheet named Work2
2) The formula="Work"&MID(D8,5,2)-1&"!H3" in D9 returns the text entry
Work1!H31
Note we do not need single quotes around the worksheet name since the name
has no spaces in it.
3)The formula =INDIRECT(D9) returns the value from the cell H31 in the
previous sheet
I leave it to use to combine this to a single formula if you so require
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim" wrote in message
...
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.

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.