reference cell from previous worksheet without "naming" worksheet

[Guest]

I have a worksheet that has multiple pages. In one cell there is a formula
that references a cell within that worksheet and then adds it to a cell from
the previous worksheet. Every week this worksheet is copied for the new week
using the "move/copy function". I would like for this cell to automatically
change to the previous worksheet without having to manually change it.

Ex. =sum(i17+'week1'!i20) This is the content of the cell in week 2.
=sum(i17+'week2'!i20) This is the content of the cell in week 3.
=sum(i17+'week3'!i20) This is the coentent of the cell in week 4.
I would like for the reference to the previous week to automatically advance
to the next week. Any suggestions???

 

[JE McGimpsey]

One way:

http://www.mcgimpsey.com/excel/udfs/prevsheet.html

 

[David Biddulph]

If you want =i17+'week1'!i20, why have you included the unnecessary SUM
function, with only one argument?

=sum(i17,'week1'!i20) would have been another way of doing it, with two
arguments to the SUM function, but with only one argument you might equally
have used =product(i17+'week1'!i20) or =average(i17+'week1'!i20) or
=min(i17+'week1'!i20) or =median(i17+'week1'!i20) or =max(i17+'week1'!i20)

Admittedly the help for SUM does say:
"SUM(number1,number2, ...)
Number1, number2, ... are 1 to 30 arguments for which you want the total
value or sum."
so 1 argument is legal and doesn't cause an error, but it is meaningless in
that case.

 

[Harlan Grove]

so 1 argument is legal and doesn't cause an error, but it is meaningless
in that case.

....

Not meaningless, just useless/pointless/inefficient/. . .