relative reference to previous sheet in formulas?

[WallaceDeShawn]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello.

the simplest way to ask this seems to be to ask the question before wasting anybody's time on details.

is this possible?
=('previous sheet relative to this one'!H22)

the longer version is this:
I have a worksheet that will grow from one sheet to 52 sheets as the weeks of the year roll by.

I will need to reference a cell in the previous week's worksheet as a starting point in the current week's sheet. these references need to be active because changes are made to old sheets as the year goes on.

these worksheets will all be named based on the week they represent and each week a new sheet will be added from a MASTER sheet.

don't want the reference to be dependent on the name of the sheet so much as its position among all of the sheets.

it *seems* like a straightforward thing, but i can't find anything on the ol' intarweb that addresses it.

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

I have a worksheet that will grow from one sheet to 52 sheets as the weeks of
the year roll by. <br><br>I will need to reference a cell in the previous
week's worksheet as a starting point in the current week's sheet. these
references need to be active because changes are made to old sheets as the
year goes on. <br><br>these worksheets will all be named based on the week
they represent and each week a new sheet will be added from a MASTER sheet.
<br><br><i>don't want the reference to be dependent on the name of the sheet
so much as its position among all of the sheets.</i> <br><br>it *seems*
like a straightforward thing, but i can't find anything on the ol' intarweb
that addresses it.

There's no native Excel way to reference a "previous" sheet.

However, since your sheets are named after weeks, you should be able to
use INDIRECT().

For instance:

Assume

1) The sheets are named Week1, Week2, etc.
2) There is a cell (say, A1) in each sheet that has the number of the
week that the sheet applies to.

Then:

=INDIRECT("'Week" & A1-1 & "'!E10")

Alternatively, you could use the sheet name to calculate the previous
sheet:

=INDIRECT("'Week" & MID(CELL("Filename",A1), FIND("]",
CELL("Filename",A1))+5, 255)-1 & "'!E10")