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.