transfer a variable balance from one worksheet to the next

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have recently started using Excel to manage my monthly budget. I have
successfully set up monthly worksheets, but would like to carry forward the
final balance from one month to the next. This will be a variable balance.
I know that probably the easy way out would be to simply copy and paste, but
if there is a function which would do this, I would like to know about it !!
 
I am assuming you have a new worksheet for each month. On the latest Months
worksheet on the beginning balance line you should put a formula to reference
the ending balance from the last months.

For example if the last months worksheet was Sheet1 and the ending balance
was on cell B20 then on the new months worksheet in the cell that you want
the ending balance from last month type the following formula

=Sheet1!B20
 
If you're willing to use a User Defined Function.......


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 20 sheets, sheet1 through sheet20...........sheet names don't
matter.

Select second sheet and SHIFT + Click last sheet

In active sheet B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

On Tue, 31 Jul 2007 13:12:02 -0700, cariad 61 <cariad
 
Gord Dibben said:
If you're willing to use a User Defined Function.......


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
Click to expand...

Eh? All the Op wants to do is to replicate the total in one cell from one
sheet into a cell on the next......
So in Sheet 2 enter the formula =Sheet1!C14 in the cell you want the b/fwd
balance to appear.... KISS!
 
Which you then have to do for 12 sheets.

If that suits you, go ahead but I don't consider that as KISS

I would rather do all sheets at once.


Gord Dibben MS Excel MVP
 
Back
Top