How can I get data to 'fill' from one sheet to another in excel 2007?

This is starting to really annoy me!

I have an Excel sheet with a field with data that rolls over from one sheet to another. ( a book of 52 time sheets)

Sheet2 references the totals on Sheet1, but开发者_Python百科 when I use 'fill across sheets' all 50 sheets all have the 'sheet1'! So it excel is coping the data, rather than the 'fills' it.

What I want to happen is; Sheet52 has a reference to Sheet51, Sheet51 to Sheet50 and so on, just like when you do a 'Fill Down' command inside a worksheet.

Thanks for for you help.

Good question. Use PrevSheet function:
http://www.mcgimpsey.com/excel/udfs/prevsheet.html
http://excel.tips.net/Pages/T003088_Relative_Worksheet_References.html

The 1st one looks better.