Running balance from previous sheet
 

I need help!

I use excel as an accounting back up for work and this worked finew until my
computer blew up (literally apparently a known problem with this type of
Dell). Anyway, I was using the formula =REFONPREVSHEET. This was so that it
would take the balance from the previous sheet and add it into the current
sheet, but with Office 2007 it will not work. I am unable to figure out why.
Each sheet is named by the date and # of that days deposit (in case of
auditing).

The way I had been doing this is as follows:

I have a starter sheet and then the first day of the new year I put the the
information into the various cells. Then the next day the total from the
first day is automatically in the next days sheet and thety are then added to
gether and then it populates the next days sheet when I open it. Also just as
a side not this is done via a template that we wrote. So we go into excel and
right click sheet 3 and then insert, which creates a new sheet and then
inserts the template which brings up the fields that need to be populated and
also the totals from the day before.

problem is it is not working any longer and I do not see a formula that is
comparable to the =REFONPREVSHEET function.

Please help I am stumped! Thanks!

Running balance from previous sheet
 

REFONPREVSHEET probably was a UDF like this one.

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

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 Tue, 29 Apr 2008 12:15:00 -0700, allyoops
wrote:

I need help!

I use excel as an accounting back up for work and this worked finew until my
computer blew up (literally apparently a known problem with this type of
Dell). Anyway, I was using the formula =REFONPREVSHEET. This was so that it
would take the balance from the previous sheet and add it into the current
sheet, but with Office 2007 it will not work. I am unable to figure out why.
Each sheet is named by the date and # of that days deposit (in case of
auditing).

The way I had been doing this is as follows:

I have a starter sheet and then the first day of the new year I put the the
information into the various cells. Then the next day the total from the
first day is automatically in the next days sheet and thety are then added to
gether and then it populates the next days sheet when I open it. Also just as
a side not this is done via a template that we wrote. So we go into excel and
right click sheet 3 and then insert, which creates a new sheet and then
inserts the template which brings up the fields that need to be populated and
also the totals from the day before.

problem is it is not working any longer and I do not see a formula that is
comparable to the =REFONPREVSHEET function.

Please help I am stumped! Thanks!