Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple wk sheets - running balance
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple wk sheets - running balance
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 Thu, 22 May 2008 09:08:01 -0700, Deb wrote: 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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple wk sheets - running balance
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" wrote: 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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple wk sheets - running balance
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. "JLatham" wrote: 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" wrote: 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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple wk sheets - running balance
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 "Deb" wrote: 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. "JLatham" wrote: 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" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
running balance | New Users to Excel | |||
to keep a running balance | Excel Discussion (Misc queries) | |||
running total from the same field on multiple sheets as i add she | Excel Worksheet Functions | |||
rent received/balance owed/running balance spreadsheet | Excel Discussion (Misc queries) | |||
running balance | New Users to Excel |