transfer a variable balance from one worksheet to the next

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 !!
0
61 (3)
7/31/2007 8:12:02 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
413 Views

Similar Articles

[PageSpeed] 20

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

"cariad 61" wrote:

> 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 !!
0
rj (29)
7/31/2007 8:18:03 PM
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
61@discussions.microsoft.com> wrote:

>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 !!

0
Gord
7/31/2007 9:04:55 PM
"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:4u8va3tmdvaqb8r7tar9p9hqa8kt3qvdog@4ax.com...
> 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
>

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! 


0
gbplinux (1141)
7/31/2007 9:24:09 PM
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

On Tue, 31 Jul 2007 22:24:09 +0100, "Gordon" <gbplinux@gmail.com.invalid> wrote:

>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! 

0
Gord
7/31/2007 10:28:26 PM
Reply:

Similar Artilces:

Trial balance showing paid documents and how applied
Our client would like to be able to generate a customer/vendor trial balance showing payments/receipts and the documents they were applied to and also showing beginning and ending balance (similar to the Solomon TB). Great Plains does not have this capability. Is there a third party solution? ...

Pass the results of a query to a variable
I created a query using the Query Builder, the result has one record with two columns. I'd run the query, and pass the value of one column to a variable. Any suggests on how this might be done or any suggestion of a better way to reach the same goal? -- Tony On Mon, 22 Mar 2010 13:06:04 -0700, Tony A. <TonyA@discussions.microsoft.com> wrote: >I created a query using the Query Builder, the result has one record with two >columns. I'd run the query, and pass the value of one column to a variable. >Any suggests on how this might be done or any suggest...

about transfer order
I created a transfer order "TO666", transfer items from store01 to store02, the system automaticly created a TO No."00001_TO666 ". And the strange thing happend: the items in "TO666" is so different with the items in "00001_TO666 ". where is the problem? with a lot of thanks. Try creating another order with the same items, if the problem still exists, most probably you have a problem in the IDs of those items. What HQ does is to map the items in the created order in Store1 to the corresponding items in HQ, then map them again to Store2 and th...

Multiple Reorder Numbers from One Vendor
Multiple suppliers for one item is great. Multiple items from the same supplier would be nice. For example, we sell spa (hot tub) supplies. One of the products we carry is basically spa perfume. It comes in four scents. I can order cases of 12--either 12 of each scent or an assorted case (3 each of all 4 scents). More example Case of 12 Scents: Scent A = 1234A Scent B = 1234B Scent C = 1234C Scent D = 1234D Assortment = 1234X I don't have an induvidual item 1234X, only items 1234A-D. I cannot add both reorder numbers to the items, as RMS won't let me add the same supplier t...

Transfering messages
I want to transfer all my messages from one PC running Vista to another running Win7 which I have Windows Mail running on. However, I have two problems: 1. On transferring some of the transferred folders contain the full message, some only contain what you might call place holders e.g my transferred Inbox has 'From' with a list of 30 or so messages but just shows an envelop and the date and time but no message 2. Is there away to transfer attachments too? -- Ron "Ron O'Brien" <castcall@ntlworld.com> wrote in message news:uBH3g6UjKHA.218...

HR Position Transfers
Is there a way to import a mass amount of position changes in GP HR? We are getting ready to effect changes for our upcoming fiscal year. There are numerous changes to our departments, positions moving from one department to another and some of the departments are altogether new. Is there a tool to import Position Change/Transfers? If not, does anyone know all the tables I would need to hit if I need to import the data? ...

naming worksheet tabs
i need to name a worksheet tab with a name that is more characters than allowed. Can you make the tab larger to accommodate a larger tab name? I know you can make the tab larger in properties/appearance/advanced; however, that does not allow me to type in more characters. -- Thanks, Sharon Thank you for your reply. -- Thanks, Sharon "Jacob Skaria" wrote: > Sorry, you cannot. > > Max length 31 > Exception characters [ ] * ? / \ > > -- > Jacob (MVP - Excel) > > > "SHARON" wrote: > > > i ne...

Deleting Old Worksheets and Journals
Hi- we have had our RMS system implemented since July 2007. I am looking to do some system clean up of old journals and old worksheets since this has not been done since we implemented. I am thinking of writing some SQL statements and executing them in HQ Administrator but I wanted to know if anyone has done this before and if they foresee any issues or have any concerns. Thanks for your help. This is a multi-part message in MIME format. ------=_NextPart_000_0063_01CAD7D1.35018830 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-prin...

Exam passed ... What next?
I passed CRM exam . Two of my colleagues also passed CRM exams. One of them passed exam two months ago, another - 1.5 months. But we have not been received any confirmation (e-mail,letter,...?) from Microsoft yet. When I become MCP two years ago, I received Congratulation-Confirmation E-mail (with MCP ID number ...) next day after exams and MCP Kit next month. We only have reports with our Examination Results. Is it OK? Can we receive something like certificate, diploma, ID ? Andy I know that in the States, I eventually received a certificate for each test that I passed. However, this too...

Indirect Method for Name Box Variable?
Can the name box use the Indirect method for defining its name? I wish to use a cell's value to define the name in the name box. I' not sure if this is possible or if I'm just getting the syntax wrong. I'm trying to group 7 days (cells) and then have Excel keep th grouping as I copy them for subsequent weeks. I don't want to manuall keep naming the groups as new weeks are added. Example in the name box: INDIRECT("week" & $A1) where A1, A2, A3, etc. will contain the sequencial values 1, 2, 3, etc Essentially, the names would end up being week1, week2, we...

Windows Easy Transfer
I am having trouble with Easy Transfer from an XPhome to XPhome installation. Actually it is the same computer after re-installing XP. I ran the Easy Transfer to back up the data onto an external drive. When I run it now on the new build I don't get the "Continue transfer in Progress" option. It does not ask if this is the new or old computer. I haven't used it before so maybe I am not using it as intended. I have user the XP File settings and transfer program for a situation just like this with no problems. Any suggestions? Hu Computer is a basic HP Pavi...

How to use variables in ranges?
can someone please explain to me how to use a variable in a rang reference? example: > > maxpolines = Application.CountIf(Worksheets("P > Data").Range("A2:A500"), srcponum) > > ...some autofilter logic.... > > Worksheets("PO Data").Select > tottoinv = Evaluate("=SUBTOTAL(9,C2:C500)") The above yields the results i want. but i would like to replace "C500 with some reference to the maximum lines as identified b 'maxpolines'. the macro recorder produced "=SUBTOTAL(9,R[-14]C:R[-1]C)". This synta looks li...

Sum up statistical results from 12 worksheets
I have 12 worksheets of certain data, that is one worksheet for every month of the year (i.e. Jan-Dec). In each worksheet/month I have already calculated the following functions/formulae: SUM MIN MAX AVERAGE STDEV Data above the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68)) Data below the AVERAGE (e.g. =ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0")) So, I now have this 13th spreadsheet that lists the results from the 12 worksheets/months. For example: Row C1:N1 Jan Feb Mar .... Dec lists the individual results from SUM Row C2:N2 Jan Feb Mar .....

Pictures are sent to stores within worksheet 250
I think it would be a good idea that item pictures are sent to the stores within the 250 worksheet (In a HQ enviroment) The procedure would be: Place the picture in the pictures folder in the hq directory Create the item in hq and assign the picture to the item Create a 250 worksheet containing the item The item is then created in the remote store database and the picture is placed in the pictures folder in the store operations directory at the remote store You can then share the pictures directory at the remote store and map a drive on all the tills to that folder and assign the net...

two domains in one outlook profile
Hey, Is there a way to choose what address shows up on the Sent From line in outlook. I have a users, all in one exchange server, who need to have their FROM LINE display a different domain then their main reply email. Both addresses are set up on their exchange account, so then can receive emails when an email is sent to user@domain1.com and user@domain2.com. By default, when they create a new email, the recipient will always see @domain1.com. Is there a way to choose what domain shows up in the FROM line so that when they need to send to certain recipients, the recipient sees ...

Transferring an order to an invoice...
We are transferring over to GP 10. I have a question about invoicing that I hope someone here can help me with. In GP 8, there was a transfer button on the Sales Order Transaction screen to transfer the order to an invoice. In GP 10, the transfer option is in a pull down menu. Can we make the transfer option a 3rd button on the screen? If someone could let me know, I would appreciate your help greatly! yes you should be able to do that if you wish using a button and a bit of vba code. patrick developer support -- This posting is provided "AS IS" with no warranties, a...

SumIf across multiple worksheets
I have 13 worksheets I want to sum quarterly. The formula I've been trying is: =SUMIF(A5:A36,"136982",'Apr 4:Jun 27'!G5:G36) but I get a #Value! error message. In column A I have contract numbers. In column G is the crew size. I want to sum all the crew size based on specific contract numbers. Any help is appreciated :) In each of the other worksheet, are the contact numbers also in column A? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Alberta Rose" <AlbertaRose@discussions.microsoft.com>...

Cannot finish balancing account
I am having a problem balancing my account in Money 2002. I can get to the list of transactions and mark them all as Cleared. When I try to click Next to finish up, nothing happens. So, I have to postpone balancing the account, because that's all I can do. I have been having problems with some of my transactions. The first time I tried to balance the account, some items did not want to be marked as Clear. I tried to delete those transactions so that I could enter them again, but I couldn't delete them either. I ended up having to repair my Money file with Microsoft's ...

Is it possible to add data to only one 1 tab of a multi-tab form
Hi I have created a multi-tab form (there are 7 tabs in all). Is it possible to set the form up in such a way that not all forms are updated at the same time? For instance, one of the forms is Contact information and another is personal information. For one student, the contact information was updated on the Monday and the personal infomration on Tuesday but there are for the same student. Thank you advance for your help. ...

System does not auto create Transfer In during Inter-store transfe
Hi, my customer running 2 stores and both store having the same interstore transfer option setting. my problem is 1 of the store does not auto generate Transfer In doc 1. store A - Transfer Out Doc store B - auto generate transfer In Doc store A uploaded 2 HQ message to HQ & store B received 2 HQ message. 2. store B - Transfer Out Doc store A - NOTHING GENERATED store B only uploaded 1 HQ message to HQ & store A received 1 HQ message. anyone got ideas how to solved this? Thanks Dennis Did you set the HQ Configuration options to auto gen...

Is there an "Execute" worksheet function?
Is there a way I can get Excel to "execute" an expression that is stored in a cell as a test string? Say C4 contains "3+4". Can I put some expression in C5 that will execute that expression and result in "7"? I tried =calculate(C4) and a few other things, but no joy. in the absence of putting = in front of c4, try this =LEFT(C4,FIND("+",C4)-1)+RIGHT(C4,LEN(C4)-FIND("+",C4)) -- Don Guillett Excel MVP SalesAid Software dguillett1@austin.rr.com "LurfysMa" <invalid@invalid.invalid> wrote in message news:o5fn83thi0g5tivf55abvhv...

TRANSFER OUT TO TRANSFER OUT
I HAVE BEEN WORKING WITH RMS FOR OVER A YEAR AND HAVE NEVER SEEN THIS PROBLEM. STORE A MAKES A TRANSFER OUT AND STORE B RECEIVES A COMPUTER TRANSFER IN THE TRANSFER OUT WINDOW INSTEAD OF A TRANSFER IN. IN ADDITION TO THAT THE TRANSFER HAS BEEN CLOSED AT STORE B'S END AUTOMITICALLY AND IT IS EMPTY. AN KNOWLEDGE AS TO WHY THIS HAPPENS THANKS Check HQ Client for errors, or from HQ Manager, view the event log for that store. You'll probably find some errors at Store B on the 401 that created the transfer. I would guess that Store A used a transfer number with more than 14 c...

Multiply values in one column by values in another column
Hi, I'm sure this must have been posted before but I can't seem to get my search terms accurate enough to find it. I have two columns: "Number of containers" and "Volume of container". I want to multiply these to give me the total volume of all containers: (A1*B1)+(A2*B2)+(A3*B3)... I could create this long formula manually, but I'm sure there must be a better way. Naturally, I could do this by creating a new column, multiplying each row and then adding the values in the third column but, again, I'm sure there must be a more elegant solution. Any suggestio...

Fixed Asset transfer for a New Company
Is selling the Fixed Asset to the new company the best option to make a transfer to a new company? Can you give us a little more detail on what you are needing to accomplish? If two companies are different legal entities there are all kinds of other rules on 'transferring' assets. Is this just a new database or are you doing an intercompany transaction? We can help, we just need a little more explanation. "FA user" wrote: > Is selling the Fixed Asset to the new company the best option to make a > transfer to a new company? ...

How to pass variable to a Sub
Hi, Private Sub Worksheet_activate() Dim X X = Sheets("DATA").UsedRange.Rows.Count Application.Run "WhatEver" End Sub I would like to pass the value of X to WhatEver Sub. How can I do that? Thanks, Alai -- Message posted from http://www.ExcelForum.com Alain, You can pass variables directly in the Run method. E.g., Application.Run "WhatEver", X -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "alainB >" <<alainB.15f339@excelforum-nospam.com> wrote in message news:alainB.15f339@e...