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

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

One method to do this is to use the indirect() function when you need to
refer to the previous sheet.
for example
say you call your sheets "week 01" etc

Set up a cell for reference (Say A1)
when you generate the new sheet
change Cell A1 to reflect the old sheet
for example in week 06 enter week 05
put equations such as
=indirect("'"&A1&"'!D3")
if you want to pull in the value from D3 in week 05 sheet

"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

Thank you both so much for taking the time to reply. I hadn't heard of the
INDIRECT function before. It worked an absolute treat.

Thank you
Sharon