Use the indirect function.

On your sheet you will need a reference to this week number
say it is in A1
and the cell you want is B1.

Sheet1!a1="2"
Sheet1!b1=if(a1=1,0,INDIRECT("Sheet"&A1-1&"!C1"))
Sheet1!c1="10"

Sheet2!a1="1"
Sheet2!b1=if(a1=1,0,INDIRECT("Sheet"&A1-1&"!B1"))
Sheet2!c1="20"

You can then use this on all sheets even the first one.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Shazzer" wrote:

I have a workbook in which a new worksheet is created each week based on a
copy of the previous week. The sheets are named from 1 to 52. Is there any
way I can refer to last week's sheet in a formula. If I was to refer to the
sheet's name I would have to update the formulas each week to refer to the
next number which I would rather avoid doing.

Thank you in advance