Select data from two worksheets to the left

[pdberger]

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.

 

[Gord Dibben]

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

 

[Jacob Skaria]

the month just to the left of the 'Last' sheet
Sheets(Sheets("Last").index-1).name

and two sheets to the left of 'Report'.
Sheets(Sheets("Report").index-2).name


If this post helps click Yes

 

[Jacob Skaria]

Please find the below UDF. To get data from the previous month use the formula
=PreviousMonth(A1)

Function PreviousMonth(varRange As Range)
For intTemp = ActiveSheet.Index - 1 To 1 Step -1
If Trim(Sheets(intTemp).Name) Like "???##" Then
PreviousMonth = Sheets(intTemp).Range(varRange.Address).Value
Exit Function
End If
Next intTemp
End Function

If this post helps click Yes

 

[pdberger]

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

 

[Dave Peterson]

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]

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