Excel Question
 

How do I export one cell's data to another sheet within the same workbook?
For example, I'm making a budget for the whole year, so I want the ending
balance from January to automatically show up in February and so on. (January
is in one sheet, February on another, and so on)

Excel Question
 

Copy/paste this UDF to a general module in your workbook.

Function PrevSheet(rg As Range)
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

Example of usage...................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP

On Sat, 12 Dec 2009 13:34:01 -0800, Brickman
wrote:

How do I export one cell's data to another sheet within the same workbook?
For example, I'm making a budget for the whole year, so I want the ending
balance from January to automatically show up in February and so on. (January
is in one sheet, February on another, and so on)

Excel Question
 

Thank you, now give me about a week to figure this out! LOL

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function PrevSheet(rg As Range)
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

Example of usage...................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP

On Sat, 12 Dec 2009 13:34:01 -0800, Brickman
wrote:

How do I export one cell's data to another sheet within the same workbook?
For example, I'm making a budget for the whole year, so I want the ending
balance from January to automatically show up in February and so on. (January
is in one sheet, February on another, and so on)

.

Excel Question
 

Good luck.


Gord

On Sat, 12 Dec 2009 16:04:01 -0800, Brickman
wrote:

Thank you, now give me about a week to figure this out! LOL

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function PrevSheet(rg As Range)
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

Example of usage...................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP

On Sat, 12 Dec 2009 13:34:01 -0800, Brickman
wrote:

How do I export one cell's data to another sheet within the same workbook?
For example, I'm making a budget for the whole year, so I want the ending
balance from January to automatically show up in February and so on. (January
is in one sheet, February on another, and so on)

.