multiple wk sheets - running balance

[Deb]

I have a form (wk sheet) for each day and I need to keep a running balance.
I currently enter the formula manually each day to reference the previous day
and carry balance forward. April 19 (4.19) I would enter ='4.18'!H38 to
reference the previous days total (April 18). Is there a formula I can enter
just once on the orginal blank form and make multiple copies that will
advance reference each day. thanks...

 

[Gord Dibben]

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

 

[JLatham]

Short answer: No, there's not a function to do that. But we can create a
workbook event handler that may work for you. Here is the code for it:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this assumes that new worksheets
'are created from the latest dated
'worksheet in the workbook.
'As if you'd chosen the worksheet
'and used Edit | Move or Copy and
'made a copy of it.
'
Dim newFormula As String
Dim monthNum As Integer
Dim dayNum As Integer
If Right(Sh.Name, 4) = " (2)" Then
'this is a freshly made copy
'set up the formula in cell H38 of the
'sheet to refer to that cell in the sheet
'it was made from
newFormula = "='" & Trim(Left(Sh.Name, _
InStrRev(Sh.Name, " (2)"))) & "'!H38"
Sh.Range("H38").Formula = newFormula
End If
End Sub


To use the code, open the workbook and right-click on the Excel Icon
immediately to the left of the word File in the menu bar. Choose [View Code]
from the list that appears. Copy the code above and paste it into the code
module presented to you. You can then close the VB Editor.

From then on what it does is check each time you select (activate) a
worksheet if that sheet's name ends with " (2)" and if it does, it sets the
formula in Cell H38 to refer to cell H38 on the sheet that was used as the
source for the copy. So you'd always want to make new sheets from the
previous day's sheet. You also need to change the name of the new sheet
pretty quickly.

Example: You'd choose sheet named 5.21 and use Edit | Move or Copy with the
"make copy" option chosen. That will create a sheet named 5.21 (2) and that
will trigger the formula revising process. You'd continue by giving the new
sheet the name 5.22 and all is done.

Hope this helps. Oh - the routine will be included in any workbooks created
from the one you put it into also.

 

[Deb]

I am almost there but I failed to give you specific info. The original TOTAL
TO DATE is H38. All days following that it becomes H37 "PREVIOUS DAY TOTAL"
and H38 is the sum of H36:H37.

H36 (TOTAL TODAY)
H37 (PREVIOUS DAY TOTAL)
H38 (TOTAL TO DATE)
The same also applies to L36, L37 and L38 ALSO Q36, Q37 and Q38.

Short answer: No, there's not a function to do that. But we can create a
workbook event handler that may work for you. Here is the code for it:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this assumes that new worksheets
'are created from the latest dated
'worksheet in the workbook.
'As if you'd chosen the worksheet
'and used Edit | Move or Copy and
'made a copy of it.
'
Dim newFormula As String
Dim monthNum As Integer
Dim dayNum As Integer
If Right(Sh.Name, 4) = " (2)" Then
'this is a freshly made copy
'set up the formula in cell H38 of the
'sheet to refer to that cell in the sheet
'it was made from
newFormula = "='" & Trim(Left(Sh.Name, _
InStrRev(Sh.Name, " (2)"))) & "'!H38"
Sh.Range("H38").Formula = newFormula
End If
End Sub


To use the code, open the workbook and right-click on the Excel Icon
immediately to the left of the word File in the menu bar. Choose [View Code]
from the list that appears. Copy the code above and paste it into the code
module presented to you. You can then close the VB Editor.

From then on what it does is check each time you select (activate) a
worksheet if that sheet's name ends with " (2)" and if it does, it sets the
formula in Cell H38 to refer to cell H38 on the sheet that was used as the
source for the copy. So you'd always want to make new sheets from the
previous day's sheet. You also need to change the name of the new sheet
pretty quickly.

Example: You'd choose sheet named 5.21 and use Edit | Move or Copy with the
"make copy" option chosen. That will create a sheet named 5.21 (2) and that
will trigger the formula revising process. You'd continue by giving the new
sheet the name 5.22 and all is done.

Hope this helps. Oh - the routine will be included in any workbooks created
from the one you put it into also.

I have a form (wk sheet) for each day and I need to keep a running balance.
I currently enter the formula manually each day to reference the previous day
and carry balance forward. April 19 (4.19) I would enter ='4.18'!H38 to
reference the previous days total (April 18). Is there a formula I can enter
just once on the orginal blank form and make multiple copies that will
advance reference each day. thanks...

 

[JLatham]

I figured there would be different cells than I used involved. I think this
code will do it for you - I tightened it up a little.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this assumes that new worksheets
'are created from the latest dated
'worksheet in the workbook.
'As if you'd chosen the worksheet
'and used Edit | Move or Copy and
'made a copy of it.
'
If Right(Sh.Name, 4) = " (2)" Then
'this is a freshly made copy
'set up the formula in cell H38 of the
'sheet to refer to that cell in the sheet
'it was made from
Sh.Range("H37").Formula = "='" & Trim(Left(Sh.Name, _
InStrRev(Sh.Name, " (2)"))) & "'!H36"
Sh.Range("L37").Formula = "='" & Trim(Left(Sh.Name, _
InStrRev(Sh.Name, " (2)"))) & "'!L36"
Sh.Range("Q37").Formula = "='" & Trim(Left(Sh.Name, _
InStrRev(Sh.Name, " (2)"))) & "'!Q36"
End If
End Sub

I am almost there but I failed to give you specific info. The original TOTAL
TO DATE is H38. All days following that it becomes H37 "PREVIOUS DAY TOTAL"
and H38 is the sum of H36:H37.

H36 (TOTAL TODAY)
H37 (PREVIOUS DAY TOTAL)
H38 (TOTAL TO DATE)
The same also applies to L36, L37 and L38 ALSO Q36, Q37 and Q38.

Short answer: No, there's not a function to do that. But we can create a
workbook event handler that may work for you. Here is the code for it:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this assumes that new worksheets
'are created from the latest dated
'worksheet in the workbook.
'As if you'd chosen the worksheet
'and used Edit | Move or Copy and
'made a copy of it.
'
Dim newFormula As String
Dim monthNum As Integer
Dim dayNum As Integer
If Right(Sh.Name, 4) = " (2)" Then
'this is a freshly made copy
'set up the formula in cell H38 of the
'sheet to refer to that cell in the sheet
'it was made from
newFormula = "='" & Trim(Left(Sh.Name, _
InStrRev(Sh.Name, " (2)"))) & "'!H38"
Sh.Range("H38").Formula = newFormula
End If
End Sub


To use the code, open the workbook and right-click on the Excel Icon
immediately to the left of the word File in the menu bar. Choose [View Code]
from the list that appears. Copy the code above and paste it into the code
module presented to you. You can then close the VB Editor.

From then on what it does is check each time you select (activate) a
worksheet if that sheet's name ends with " (2)" and if it does, it sets the
formula in Cell H38 to refer to cell H38 on the sheet that was used as the
source for the copy. So you'd always want to make new sheets from the
previous day's sheet. You also need to change the name of the new sheet
pretty quickly.

Example: You'd choose sheet named 5.21 and use Edit | Move or Copy with the
"make copy" option chosen. That will create a sheet named 5.21 (2) and that
will trigger the formula revising process. You'd continue by giving the new
sheet the name 5.22 and all is done.

Hope this helps. Oh - the routine will be included in any workbooks created
from the one you put it into also.

I have a form (wk sheet) for each day and I need to keep a running balance.
I currently enter the formula manually each day to reference the previous day
and carry balance forward. April 19 (4.19) I would enter ='4.18'!H38 to
reference the previous days total (April 18). Is there a formula I can enter
just once on the orginal blank form and make multiple copies that will
advance reference each day. thanks...