Formula referencing the previous sheet

[capblasters]

I have a file with a sheet for each week (52 all in) which is a form that gets filled in weekly. One cell on sheet 2 ('Sheet2'!A10 for example) represents the value of the previous week ('Sheet1'!A10) and adds half of the value of 'Sheet1'!A8. So, the formula in 'Sheet2'!A10 = 'Sheet1'!A10 + 0.5 * 'Sheet1'!A8

What I am looking for is a way to write the formula above such that when it is copied from 'Sheet2' to the other 52 sheets, the 'Sheet2' is updated to 'Sheet3', 'Sheet4' etc. such that (for example) the cell 'Sheet10'!A10 = 'Sheet9'!A10 + 0.5 * 'Sheet9'!A8.

Thanks,

Graham

 

[kvsrinivasamurthy]

Formula in A10 of Sheet2

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]Sheet",CELL("filename",A1))+6,255)-1&"!A10")+0.5*INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]Sheet",CELL("filename",A1))+6,255)-1&"!A8")

Then copy to A10 of other sheets

 

[capblasters]

Thanks for this. I got this to work with a couple of changes. The first is a missing space after the work Sheet in the first argument for each of the INDIRECT functions. The second is an apostrophe in front of the exclamation point of each of the cell references. The formula I used is shown below. I would never have got there without the help. Thanks!
=INDIRECT("'Sheet "&MID(CELL("filename",A1),FIND("]Sheet",CELL("filename",A1))+6,255)-1&"'!A10")+0.5*INDIRECT("'Sheet "&MID(CELL("filename",A1),FIND("]Sheet",CELL("filename",A1))+6,255)-1&"'!A8")