I am using Excel 2003. I have a workbook to keep track of daily expense/revenue figures. I have a different worksheet for each month. At the end of each month I total each revenue and expense item. I want to have a year to date figure for each column that carries over to the next worksheet. How do I do this?

"jackreacher" wrote:
I am using Excel 2003. I have a workbook to keep track
of daily expense/revenue figures. I have a different
worksheet for each month. At the end of each month I
total each revenue and expense item. I want to have a
year to date figure for each column that carries over
to the next worksheet. How do I do this?

If you are willing to put the subtotals for each month into the same fixed
cell on all worksheets, the solution can be simple. The easiest place is
row 1 (subtotals on top, not on the bottom). That's also most convenient
for the human "computer". For example:

=SUM(Sheet1:Sheet12!B1)-SUM(Sheet1:Sheet12!C1)

where B1 is the subtotal of revenues and C1 is the subtotal of expenses.

Note: Very few functions allow "3D references" like Sheet1:Sheet12!B1. SUM
is one of them.

If you insist on putting the subtotals into difference cells on each
worksheet, there are more robust ways to deal with it. But why bother?
"Everything should be as simple as possible, but not simpler" (Einstein).

Let me clarify. All the monthly worksheets are formatted the same. C39 is the monthly total of revenue. There are three monthly expense totals, E39, F39, I39. Net revenue is K39. I want to keep these monthly totals and have another row that keeps a YTD total for each category that carries over into the other worksheets.

"jackreacher" wrote:
Let me clarify. All the monthly worksheets are formatted
the same. C39 is the monthly total of revenue. There are
three monthly expense totals, E39, F39, I39. Net revenue
is K39. I want to keep these monthly totals and have another
row that keeps a YTD total for each category that carries
over into the other worksheets.

1. In each worksheet starting with Sheet2, enter the following formula into
C40:
=IF(C39="","",C39+Sheet1!C39)

Replace "Sheet1" with the name of the previous-month's worksheeet.

Note: In Sheet1, you might enter the formula =C39 into C40. It is
redundant. But it might be aesthetically pleasing for all worksheets to
have the same structure.

2. Then, in each worksheet, copy C40 into E40, F40, I40 and K40. The result
in E40, for example, will look like:
=IF(E39="","",E39+Sheet1!E39)

The condition IF(C39="","",...) prevents the YTD from propagating into
monthly worksheets before there is data for the month.

Just something to think about.

Here is a UDF that returns values from the previous sheet.

Function PrevSheet(rg As Range)
'accounts for more than one workbook open
'and has hidden sheets
'Bob Phillips October 4, 2009
Dim n As Variant
With Application.Caller.Parent
n = .Index
Do
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
Exit Do
ElseIf TypeName(.Parent.Sheets(n - 1)) < "Chart" And _
.Parent.Sheets(n - 1).Visible = xlSheetVisible Then
PrevSheet = .Parent.Sheets(n - 1).Range(rg.Address).Value
Exit Do
End If
n = n - 1
Loop
End With
End Function

usage is: =prevsheet(cellref)


Gord



On Wed, 8 Jan 2014 12:40:03 +0000, jackreacher
wrote:


'joeu2004[_2_ Wrote:
;1615617']"jackreacher" wrote:-
I am using Excel 2003. I have a workbook to keep track
of daily expense/revenue figures. I have a different
worksheet for each month. At the end of each month I
total each revenue and expense item. I want to have a
year to date figure for each column that carries over
to the next worksheet. How do I do this?-

If you are willing to put the subtotals for each month into the same
fixed
cell on all worksheets, the solution can be simple. The easiest place
is
row 1 (subtotals on top, not on the bottom). That's also most
convenient
for the human "computer". For example:

=SUM(Sheet1:Sheet12!B1)-SUM(Sheet1:Sheet12!C1)

where B1 is the subtotal of revenues and C1 is the subtotal of
expenses.

Note: Very few functions allow "3D references" like Sheet1:Sheet12!B1.
SUM
is one of them.

If you insist on putting the subtotals into difference cells on each
worksheet, there are more robust ways to deal with it. But why bother?

"Everything should be as simple as possible, but not simpler"
(Einstein).

Let me clarify. All the monthly worksheets are formatted the same. C39
is the monthly total of revenue. There are three monthly expense totals,
E39, F39, I39. Net revenue is K39. I want to keep these monthly totals
and have another row that keeps a YTD total for each category that
carries over into the other worksheets.