| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select data from two worksheets to the left
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Select data from two worksheets to the left
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Select data from two worksheets to the left
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Select data from two worksheets to the left
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Select data from two worksheets to the left
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Select data from two worksheets to the left
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 "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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Select data from two worksheets to the left
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 --------------- Jacob Skaria "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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| when inserting new worksheets they read right to left not left to. | Setting up and Configuration of Excel | |||
| Can I select data in several Worksheets for a Pivot Table? | Charts and Charting in Excel | |||
| Only change left header on all worksheets | Excel Discussion (Misc queries) | |||
| How to point to (select) a cell to the left from a cell where I enter the = equal sign? | Excel Discussion (Misc queries) | |||
| Buttons on excel worksheets relocating to the left when I print. . | Excel Discussion (Misc queries) | |||
Hybrid Mode
