Works like a charm. Tastes great. Less filling. Thanks to you both.

"Dave Peterson" wrote:

Don't put this code behind a worksheet.

Put it in its own General module:
Inside the VBE:
Insert|Module
(and remove it from whereever you put it before)

pdberger wrote:

Many thanks, but I'm afraid I just ain't there yet. I copied your code to
Sheet 3, which now has the 'Option Explicit' declaration, then the PrevSheet
and PrevSheet2 functions.

However, I just can't figure out how to invoke them. I tried
=PrevSheet(A1)
but got nothing.

Do I have to write a little program to call the function? Is that the step
I'm missing?

TIA

Peter

"Gord Dibben" wrote:

To reference first sheet to left.

Function PrevSheet(rg As Range)
Application.Volatile
N = Application.Caller.Parent.Index
If N = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(N - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(N - 1).Range(rg.Address).Value
End If
End Function

For second sheet to left

Function PrevSheet2(rg As Range)
Application.Volatile
N = Application.Caller.Parent.Index
If N = < 3 Then
PrevSheet2 = CVErr(xlErrRef)
ElseIf TypeName(Sheets(N - 2)) = "Chart" Then
PrevSheet2 = CVErr(xlErrNA)
Else
PrevSheet2 = Sheets(N - 2).Range(rg.Address).Value
End If
End Function


Gord Dibben MS Excel MVP


On Thu, 16 Apr 2009 17:03:01 -0700, pdberger
wrote:

Good afternoon --

I'm storing a lot of monthly data, 1 worksheet per month. I want to create
a summary worksheet that MOSTLY pulls a rolling average of the last three
months' worth of data. My approach is this -- I create worksheets as follows:

Nov08 Dec08 First Jan09 Feb09 Mar09 Last Report Apr09 May09

On the 'Report' page, most of the formulas average the data in the sheets
between 'First' and 'Last' like this: =AVERAGE('FIRST:LAST'!A1). That way,
I can simply slide sheets in and out of the area I want to average. Works
great, less filling.

THE PROBLEM is that I would like some of the cells to always pick the last
month -- the month just to the left of the 'Last' sheet, and two sheets to
the left of 'Report'. Otherwise, I have to remember to change the formula in
just those cells every time. Any clever way?

Thanks in advance.



--

Dave Peterson