Excel: reference a previous worksheet

Recon

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 something like that?

Thanks

TrueDub

Yes, there is.

To refer to a different worksheet, simply put the name in front of the reference i.e.

=Sheet1!A1

to refer to the first cell of sheet1.

Probably an easy way for you to do it is to do the following

Click on your Starting KM cell
Enter an = sign
Without pressing enter, bring up the old sheet and click on the cell you want to reference (End KM)
Press enter, and your link will be established.

krd

I'm sure it can be done. I don't have Excel to play around with. And it might be a little fiddly to do - There's always several ways you can do this.

Try this and see if it works: I can't be sure it will work because I haven't tried it. It looks like it will work. If it works it may be as simple as =PrevSheet!H29 to get it to get the previous weeks mileage. The INDIRECT looks a little bit of a pain.

Start Excel and open the workbook in which you want to start using this.
Now define these names (Insert, Name, Define):

AllSheets
=GET.WORKBOOK(1+0*now())
Gets an array of all sheets in the workbook

ThisSheet
=GET.CELL(32+0*now(),indirect(”rc”,False))
Gets the name of the sheet the name is used in.

PrevSheet
=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)-1+0*now())
Gives the name of the worksheet to the immediate left of the sheet where this name is used.

NextSheet
=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)+1+0*now())
Gives the name of the worksheet to the immediate right of the sheet where this name is used.

The 0*Now() is added to ensure the names are “volatile” and get recalculated on every calculation of Excel.

To refer to cell B1 on the previous worksheet, use this formula:

=INDIRECT(”‘”&PrevSheet&”‘!”&CELL(”address”,B1))

Likewise for the next worksheet:
=INDIRECT(”‘”&NextSheet&”‘!”&CELL(”address”,B1))

To get the name of the previous sheet in a cell, use:
=PrevSheet

One serious warning is needed though.
Do not copy any cell that uses any of these defined names to another worksheet. Excel will crash!

I got that from http://www.methodsinexcel.co.uk/Articles/one%20before.html

Recon

Thanks for the replies, didn't have much luck with them though.

krd

I think there is a very simple way of doing it - I just don't have Excel in front of me to try it.

Try posting the question to this forum http://www.mrexcel.com/forum/forumdisplay.php?f=10

Recon

That's great, thanks.

Recon

This was the solution. Create a module with the following function in it.

Function SHEETOFFSET(offset, Ref)
'   Returns cell contents at Ref, in sheet offset
    Application.Volatile
    With Application.Caller.Parent
        SHEETOFFSET = .Parent.Sheets(.Index + offset) _
         .Range(Ref.Address).Value
    End With
End Function

To use it in your excel file, put

=SHEETOFFSET(-1,A1)

in the relevant cell, where '-1' is the number of worksheets you want to go back and 'A1' is the cell you want to copy.

You need to have macros enabled though.

EDIT: here's the link for where I got it
http://spreadsheetpage.com/index.php/tip/a_custom_function_for_relative_sheet_references/