Relative worksheet references

[LeftNoTracks]

I'm making a monthly timesheet, just for myself. I need to total
month's current overtime with the previous month's running tota
overtime. each month is its own worksheet, named by month and year (eg
"January 2004"). I'd like the reference to the cell in the other mont
to be relative, so a simple duplication of a month's sheet followed m
clearing the data will allow me to go on to the next month.

So, how to aI make a worksheet reference relative? Nothing in MS Exce
help seems to refer to this

 

[JE McGimpsey]

XL can't do this natively, but you can use a User Defined Function:

Public Function PrevSheet(Optional rRng As Excel.Range) As Variant
Dim ndx As Integer
Application.Volatile
If rRng Is Nothing Then Set rRng = Application.Caller
ndx = rRng.Parent.Index
If ndx > 1 Then
Set PrevSheet = Sheets(ndx - 1).Range(rRng.Address)
Else
PrevSheet = CVErr(xlErrRef)
End If
End Function

If you put this

=PrevSheet()

in sheet 'February 2004' cell A1, it will return the value in 'January
2004', cell A1 (assuming 'January 2004' is immediately to the left of
'February 2004')

Or you can put this in 'February 2004', cell A1:

=PrevSheet(B1)

and it will return the value in 'January 2004', cell B1.

If you're not familiar with UDF's check out:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[LeftNoTracks]

THanks, that worked. Took some time to figure it out. This is a bit ove
my head and I can't quite tell what it's doing.

I also can't find the function in my workbook, but I know it's ther
becuase it works