Reference a previous worksheet

[AlanKel]

I need to keep track of my mileage in the work van, by taking note of it the same day each week. The expenses here are done on an excel file, one worksheet per week.

Example
http://img9.imageshack.us/img9/3157/expenses.jpg

I want to put in a formula for the "Start km" for each week to show the "End km" of the previous week. I know to do it for one week would be to use

=wk9!H29

But to do this for the rest of the year would be annoying changing the formula to wk10, wk11 etc...

Is there a way to just have

=previousworksheet!H29 or =(ThisSheet -1)!H29 or something like that?

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

It is possible:

http://spreadsheetpage.com/index.php/tip/a_custom_function_for_relative_sheet_references/

but it would be much better to record all your data on one worksheet (with a column for the week number if you want).

 

[AlanKel]

That's great thanks for the quick reply. Would I put the code for that function in the regular Microsoft Visual Basic code editor in the "This Workbook" section? I've tried that but I'm getting the "#NAME?" error in the cell I put "=sheetoffset(-1,A1)"

As for your idea for keeping everything on the same worksheet. That one is out of my hands, the boss wants them all kept on separate worksheets the way he set them up. I'm just trying to make it a little easier for myself with bits and pieces of code here and there.

Thanks

 

[Andrew Poulsom]

You put the function in a General module like Module1.

 

[AlanKel]

That's perfect, thank you very much. Working now