Referencing 1st preceding sheet in excel

As there is already a solution for this, I assume it's not possible in a formula.

See: http://j-walk.com/ss/excel/tips/tip63.htm 

Of course you could change this to always use an offset of -1 and maybe other defaults you want to use like look at the same cell in that sheet.

If you need help implementing that, let me know.

I have made an example, hope that's clearer, if needed.
Test.xls

Could i use an indirect function and change the sheet name to reflect ie a 1 week difference based on let's say a 52 week year?

is this what you mean:

=INDIRECT(ADDRESS(ROW(),COLUMN(),,,"week"&(WEEKNUM(DATEVALUE("2012-03-20"))-1)))

Select allOpen in new window

This references the current cell in the "week11" sheet, since weeknum for today is 12.

The literal date can be replaced by a cell ref, or a call to TODAY() if that's what you need.

It can apparently also be done with a formula that gets the full reference to a cell, for example in cell A25 I've put:

=CELL("filename",A1)

Select allOpen in new window

Then in A26:

=MID(A25,FIND("]",A25)+1,LEN(A25)-FIND("]",A25))

Select allOpen in new window

This gets the sheet name which in this case was: week12

Then in cell A27:

=LEFT(A26,4)&INT(MID(A26,5,LEN(A26)-4))-1

Select allOpen in new window

That returns the previous week, so now you can use that cell as the 5th argument to the ADDRESS call. Maybe that's more applicable to your situation? It's a pain with the extra cells though, you could write each reference out of course but it might become painfully slow, I don't really know.

thanks