How do I pull data from a previous worksheet?
 

I have a file with 31 worksheets, one for each day of my accounting period.
Each worksheet is divided into 2 parts, daily and period to date. I am
trying to calculate the rolling totals each day. I can do it, but reentering
the manipulated formulas for each day is very time consuming. Isn't there a
way to pull data from a previous worksheet by using a command "previous"? I
could've sworn I've seen it somewhere before.

Scott

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1.
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

Say you have 31 sheets, sheet1 through sheet31.

Select sheet2 and SHIFT + Click sheet31

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

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

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP


On Sun, 27 Feb 2005 15:55:03 -0800, "Scott S" <Scott
wrote:

I have a file with 31 worksheets, one for each day of my accounting period.
Each worksheet is divided into 2 parts, daily and period to date. I am
trying to calculate the rolling totals each day. I can do it, but reentering
the manipulated formulas for each day is very time consuming. Isn't there a
way to pull data from a previous worksheet by using a command "previous"? I
could've sworn I've seen it somewhere before.

Thanks a million!




"Gord Dibben" wrote:

Scott

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1.
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

Say you have 31 sheets, sheet1 through sheet31.

Select sheet2 and SHIFT + Click sheet31

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

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

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP


On Sun, 27 Feb 2005 15:55:03 -0800, "Scott S" <Scott
wrote:

I have a file with 31 worksheets, one for each day of my accounting period.
Each worksheet is divided into 2 parts, daily and period to date. I am
trying to calculate the rolling totals each day. I can do it, but reentering
the manipulated formulas for each day is very time consuming. Isn't there a
way to pull data from a previous worksheet by using a command "previous"? I
could've sworn I've seen it somewhere before.