Totals in one cell

What would be the formula if possible for this setup I'm trying to use?

Cell A       Cell B
    2            67

When I input 2 in cell A, I want cell B to change to 69.  I know how to do 
that but the problem I'm running into is when I come back the following 
month, and put lets say 6 in cell A, I want add 6 to the already 69 to make 
75. So, another words almost like a running balance but within cell b.

So, another words, I want to add cell a to cell b to create a new total, 
then take that new total the next month and add cell a to create a new total 
but within cell b.  Is this possible?  Thank you.

TL
0
Utf
5/11/2010 1:55:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
864 Views

Similar Articles

[PageSpeed] 28

Hi,

This works for A1 & B1 but you can change these to what you want. Right 
click your sheet tab, view code and paste this code in on the right

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
    If IsNumeric(Target.Value) Then
        Application.EnableEvents = False
        Range("B1").Value = Range("B1").Value + Target.Value
        Application.EnableEvents = True
    End If
End If
End Sub
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"tl" wrote:

> What would be the formula if possible for this setup I'm trying to use?
> 
> Cell A       Cell B
>     2            67
> 
> When I input 2 in cell A, I want cell B to change to 69.  I know how to do 
> that but the problem I'm running into is when I come back the following 
> month, and put lets say 6 in cell A, I want add 6 to the already 69 to make 
> 75. So, another words almost like a running balance but within cell b.
> 
> So, another words, I want to add cell a to cell b to create a new total, 
> then take that new total the next month and add cell a to create a new total 
> but within cell b.  Is this possible?  Thank you.
> 
> TL
0
Utf
5/11/2010 2:05:01 PM
See this:

http://mcgimpsey.com/excel/accumulator.html

-- 
Biff
Microsoft Excel MVP


"tl" <tl@discussions.microsoft.com> wrote in message 
news:F79361F2-E0AE-46B0-BADF-E240385205CF@microsoft.com...
> What would be the formula if possible for this setup I'm trying to use?
>
> Cell A       Cell B
>    2            67
>
> When I input 2 in cell A, I want cell B to change to 69.  I know how to do
> that but the problem I'm running into is when I come back the following
> month, and put lets say 6 in cell A, I want add 6 to the already 69 to 
> make
> 75. So, another words almost like a running balance but within cell b.
>
> So, another words, I want to add cell a to cell b to create a new total,
> then take that new total the next month and add cell a to create a new 
> total
> but within cell b.  Is this possible?  Thank you.
>
> TL 


0
T
5/11/2010 2:09:08 PM
Are you sure you want to do this? 

Think about it after reading the following. 

You can have a cumulative total in a cell if you have a 
separate source cell for adding a new total to the original. 

Use at your own risk.  I am Posting this just to show you how it can 
be done, not as a good solution. You would be much better off to 
have another column so you can keep track of past entries. 

Goes like this:  =IF(CELL("address")="$C$4",C4+D4,D4) 

Enter this in cell D4 and then in Tools>Options>Calculation check 
Iterations and set to 1. 

Now when you change the number in C4, D4 will accumulate. 

Note 1.  If C4 is selected and a calculation takes place anywhere in 
the Application D4 will update even if no new number is entered in 
C4.  NOT GOOD. 

Note 2.  This operation is not recommended because you will have no 
"paper trail" to follow.  Any mistake in entering a new number in C4 
cannot be corrected.  NOT GOOD. 

To clear out the accumulated total in D4 and start over, select D4 
and Edit>Enter. 

Check out Laurent Longre's MoreFunc.xla.  Has a Function RECALL 
which does what you want without the re-calculation problem, but 
again there is no "paper trail" for back-checking in case of errors 
in data input. 

http://longre.free.fr/english/func_cats.htm 

Also see John McGimpsey's site for VBA method and the same caveats as above.

http://www.mcgimpsey.com/excel/accumulator.html


Gord Dibben Excel MVP 


On Tue, 11 May 2010 06:55:01 -0700, tl <tl@discussions.microsoft.com> wrote:

>What would be the formula if possible for this setup I'm trying to use?
>
>Cell A       Cell B
>    2            67
>
>When I input 2 in cell A, I want cell B to change to 69.  I know how to do 
>that but the problem I'm running into is when I come back the following 
>month, and put lets say 6 in cell A, I want add 6 to the already 69 to make 
>75. So, another words almost like a running balance but within cell b.
>
>So, another words, I want to add cell a to cell b to create a new total, 
>then take that new total the next month and add cell a to create a new total 
>but within cell b.  Is this possible?  Thank you.
>
>TL

0
Gord
5/11/2010 2:21:36 PM
Would I be able to use this for a series of cells?  I have several different 
rows that I'm needing to keep a total of.  I have it working for one total in 
one cell in one row using the accumulator but I have several rows of totals. 

TL

"T. Valko" wrote:

> See this:
> 
> http://mcgimpsey.com/excel/accumulator.html
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "tl" <tl@discussions.microsoft.com> wrote in message 
> news:F79361F2-E0AE-46B0-BADF-E240385205CF@microsoft.com...
> > What would be the formula if possible for this setup I'm trying to use?
> >
> > Cell A       Cell B
> >    2            67
> >
> > When I input 2 in cell A, I want cell B to change to 69.  I know how to do
> > that but the problem I'm running into is when I come back the following
> > month, and put lets say 6 in cell A, I want add 6 to the already 69 to 
> > make
> > 75. So, another words almost like a running balance but within cell b.
> >
> > So, another words, I want to add cell a to cell b to create a new total,
> > then take that new total the next month and add cell a to create a new 
> > total
> > but within cell b.  Is this possible?  Thank you.
> >
> > TL 
> 
> 
> .
> 
0
Utf
5/11/2010 3:49:01 PM
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Const WS_RANGE As String = "A1:A10" '<<<<<<adjust to suit

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If IsNumeric(.Value) Then
                Application.EnableEvents = False
                .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub


Gord Dibben  MS Excel MVP

On Tue, 11 May 2010 08:49:01 -0700, tl <tl@discussions.microsoft.com> wrote:

>Would I be able to use this for a series of cells?  I have several different 
>rows that I'm needing to keep a total of.  I have it working for one total in 
>one cell in one row using the accumulator but I have several rows of totals. 
>
>TL
>
>"T. Valko" wrote:
>
>> See this:
>> 
>> http://mcgimpsey.com/excel/accumulator.html
>> 
>> -- 
>> Biff
>> Microsoft Excel MVP
>> 
>> 
>> "tl" <tl@discussions.microsoft.com> wrote in message 
>> news:F79361F2-E0AE-46B0-BADF-E240385205CF@microsoft.com...
>> > What would be the formula if possible for this setup I'm trying to use?
>> >
>> > Cell A       Cell B
>> >    2            67
>> >
>> > When I input 2 in cell A, I want cell B to change to 69.  I know how to do
>> > that but the problem I'm running into is when I come back the following
>> > month, and put lets say 6 in cell A, I want add 6 to the already 69 to 
>> > make
>> > 75. So, another words almost like a running balance but within cell b.
>> >
>> > So, another words, I want to add cell a to cell b to create a new total,
>> > then take that new total the next month and add cell a to create a new 
>> > total
>> > but within cell b.  Is this possible?  Thank you.
>> >
>> > TL 
>> 
>> 
>> .
>> 

0
Gord
5/11/2010 4:25:26 PM
Thank you Gord Dibben and everybody else that provided me with help.  
Everything worked great!

"Gord Dibben" wrote:

> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> 
>     Const WS_RANGE As String = "A1:A10" '<<<<<<adjust to suit
> 
>     If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
>         With Target
>             If IsNumeric(.Value) Then
>                 Application.EnableEvents = False
>                 .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
>                 Application.EnableEvents = True
>             End If
>         End With
>     End If
> End Sub
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Tue, 11 May 2010 08:49:01 -0700, tl <tl@discussions.microsoft.com> wrote:
> 
> >Would I be able to use this for a series of cells?  I have several different 
> >rows that I'm needing to keep a total of.  I have it working for one total in 
> >one cell in one row using the accumulator but I have several rows of totals. 
> >
> >TL
> >
> >"T. Valko" wrote:
> >
> >> See this:
> >> 
> >> http://mcgimpsey.com/excel/accumulator.html
> >> 
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >> 
> >> 
> >> "tl" <tl@discussions.microsoft.com> wrote in message 
> >> news:F79361F2-E0AE-46B0-BADF-E240385205CF@microsoft.com...
> >> > What would be the formula if possible for this setup I'm trying to use?
> >> >
> >> > Cell A       Cell B
> >> >    2            67
> >> >
> >> > When I input 2 in cell A, I want cell B to change to 69.  I know how to do
> >> > that but the problem I'm running into is when I come back the following
> >> > month, and put lets say 6 in cell A, I want add 6 to the already 69 to 
> >> > make
> >> > 75. So, another words almost like a running balance but within cell b.
> >> >
> >> > So, another words, I want to add cell a to cell b to create a new total,
> >> > then take that new total the next month and add cell a to create a new 
> >> > total
> >> > but within cell b.  Is this possible?  Thank you.
> >> >
> >> > TL 
> >> 
> >> 
> >> .
> >> 
> 
> .
> 
0
Utf
5/11/2010 5:21:01 PM
Reply:

Similar Artilces:

Excel 2007 format a cell
Hi, Does anyone know how to format a cell automatically ? I have a cell that changes fill color if its a weekday (eg. A1) but is there a easy way to change A2 as well (thats the date - 1..31) ? TIA MJ Use the same formula for the conditional formatting formula that you used for the first cell. Depending on what you're doing, just using the absolute address ($A$1 instead of A1) may be enough. Mike Johnson wrote: > > Hi, > > Does anyone know how to format a cell automatically ? > > I have a cell that changes fill color if its a weekday (eg. A1) but is there &...

Two Exchange Profiles for one person
I have a problem in that I need to set up to "individuals" under exchage that are in fact the same person and have that person access either profile (XP-Pro office 2003) with out needing to log in as either "individual" each time he changes between the two outlook profiles. I believe it can be done using accounts but the user has an issue with remembering to select the right account and sending emails out from the wrong version of himself is not an option. Hence the use of two separate outlook profiles to handle the two separate instances of himself. The problem is h...

Can't open more than one file.
I have looked and looked but only seen one or two vague references t this problem. This has started only recently...I can only open one excel file at time. That is, if I have an excel file open and I click on another, th first one closes. I used to be able to have multiple excel files open. I am starting to think it may be a memory problem but don't know how t prove it. (Using win98). Thanks for any help -- adnum ----------------------------------------------------------------------- adnum8's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2928 View this t...

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

Selecting a large range of cells?
I'm not too strong with Excel so this might be an easy question. I have a large workbook with about 50K rows and about 100 columns. I need to sort this large range of data by different columns from time to time. I know how to sort it by just holding down the left mouse button and dragging the highlighted area to include the range. Then I go to "data" / "Sort". This just takes way too long to do each time for a range this size. I hope there is a better way to include this large range without having to "grab" it all manually. Is there? THANKS. Type ...

Several (x, y) coordinated lines on one graph
I am trying to plot for the general (x, y) coordinated dataset. For example, Series A (1, 50) (8, 90) (10, 300) (300, 500) Series B (3, 80) (400, 90) Series C (30, 80) (200, 120) and so on. The data is store on single worksheet Series Name x coordinate y coordinate A 1 50 A 8 90 A 10 300 A 300 500 B 3 80 B ...

Inserting a paragraph from one word
How can i insert a paragraph when I type in just one word. i.e. type in cycledim and that would be replaced with a description of what cycledim is. Thanks Create an AutoCorrect entry that replaces "cycledim" with whatever text you want. -- Stefan Blom Microsoft Word MVP "PawlingJohn" <PawlingJohn@discussions.microsoft.com> wrote in message news:8638BE82-BCDA-4AC9-AEB3-248CEF7E2606@microsoft.com... > How can i insert a paragraph when I type in just one word. i.e. type in > cycledim and that would be replaced with a description of what ...

OLE notes for sales order attaches to more than one order
I am attaching an OLE note to a sales order. After I do this, the note appears on about 10 different sales orders at the same time. If you delete the note from any of the orders, it deletes it from all of the orders. Order numbers are not sequential, not the same customers, not the same sales batch, etc. I can't see any common factor in this particular group of orders. We have been running GP 7.5 for about three years, and never had a problem with notes until recently. The note index value is the same in SOP10100. There is a knowledgebase article on how to update the notes mas...

how to use one exchange mailbox on home and work pc's
anyone know how i can make my home pc's outlook popup task reminders that only have a specific category e.g. "Home"? i connect to my works exchange server thru a vpn. ...

EXCEL: Separating data from 1 cell into multiple cells
I have thousands of cells in a report with information that I need to separate into different cells. Ex. one cell: Arch | South | All Points | Extreme I need an automated way to get each of the 4 names separated by "|" into its own cell. Any suggestions? Thanks, Heather (heatherjoslyn@yahoo.com) Hi, Highlight the column, Text to columns, Delimited, next, check only the Other box and copy your | in the box beside it, next, finish "HeatherJ" wrote: > I have thousands of cells in a report with information that I need to > separate in...

A Named cell to be in a formula for a code
Hi! I have a named cell -> combo_chk (cell J3) This combo_chk changes because of FOR..NEXT from code but the style i always like this : 4,5,12,40,45 I have 5 columns with values in the rows A B C D E 1 12 40 41 45 5 6 10 11 40 I want to find if the 1 on column A is including in the combo_chk then if the 12 on column B is including in the combo_chk e.t.c. If it is found return '1' otherwise return '0'. So for the above example i would get for the first row -> 0 1 1 0 1 for the second row -> 1 0 0 0 1 because the values from the column B,C and E are incl...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

Finding Dups in one column then delete leaving one
I have a list of Vendor numbers in Column A, I need to find the duplicates that are in column A then delete the duplicates but leave one. How and where do I write the formula for this? If you are using Excel 2007, select column A, then select Remove Duplicates on the Data ribbon. In Excel 2003 & earlier, use 2 helper (empty) columns on the sheet that has the duplicates. Enter this formula in row 1 in one helper column: =Row() Enter this formula in row 1 in the other helper column: =IF(COUNTIF($A$1:$A1,A1)>1,0,1) Copy these two cells down through the last row o...

Copying rows from one Excel spreadsheet to another
At my office, we use Excel 2000 (9.0.3821 SR-1). I have a daily spreadsheet that I maintain and add comments to one of the cells. At the end of the week, I maintain a weekly spreadsheet in which I copy the rows in which I have comments to the weekly spreadsheet. Sometimes, it allows me to also copy the comments, and at other times, it does not, and then I have to re-add them. Can you come up with a reason and a solution? What do you mean by "allows me"? If you do a regular copy as in ctrl + C it will copy everything, if you only want to copy comments, copy and when pasting do e...

separate contents of one cell into multiple cells
Hi ! Is there a way to separate a content in one cell into mutiple cels?? I have this: Portugal,KM25,2,euros,2002 what I=B4m looking for is separate all this between comma like Portugal in one cell km25 in other cell 2 in other and so. Thanks Horacio Try Text to column under Data (2003) using coma as the delimiter. HTH Regards, Howard "Horacio" <h.anastacio@gmail.com> wrote in message news:61d06312-3aee-45da-9bb0-72ef25430fe7@a32g2000yqm.googlegroups.com... Hi ! Is there a way to separate a content in one cell into mutiple cels?? I have t...

Too Many Different Cell Format
I got error message " Too Many Different Cell Format". Anyone know what caused it? Thank you. for further assistance on this particular issue, click on https://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&tid=1d4df007-3dc4-41f0-8182-aafe32548d4f&cat=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1 -- learn - n - enjoy ------------------------------------- click yes below, if it works for you "Bamboo" wrote: > I got error message " Too Many Different Cell Format". Anyone know ...

Excel 2007 changes links in cells
Hello guys, when I enter link to TXT file in the cell (e.g. D:\aa\ss\ko-hs-048-332-09\11ss.txt), for some reason it changes to C:\Users\Eddie\AppData\Roaming\Microsoft\Excel\ko-hs-048-332-09\11ss.txt after some time. It seems to me it's something like Temporary Internet Files in IE... Any ideas how to keep the original link assuming that I haven't changed the location of the file? Thanks, Milan -- Milan Bortel MCP, MCAS, MCTS GOPAS Computer Training Center Brno, Czech Republic Saved from a previous post: A few people have said that this has stopped a s...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

pivot table
I have excel 2002. I use all the time pivot tables and I have the problem that in the previous versions of excel I did not have.. Whenever I have a pivot table and I want to use the information that is included in it, when I make a reference to one of the cells of the pivot table, instead of getting just the value included in the cell and for example the reference " =J2", what I get is something like the following: " =+GETPIVOTDATA("a",$I$10,"a",1) ". Therefore, for example, if I want to drag down that formula I always have to unlock the cell (take out t...

cell height changing when using autofill
Hey everyone, been experiencing a very frustrating problem the past couple of days. I am using excel to make invoices for print. I am using it for this becasue I can print them in sequential numbers. My problem is that I am trying to copy the entire page and autofill it down a few times, but on the autofilled pages the cell heights of a few of the cells changes on me and messes up the entire job. I'm not understanding why it is doing this becasue I have it set to autofill the cell formatting as well as contents. Any suggestions? I couldn't get the rowheights to change (xl2...

Add Sheet Cells into 1 sheet
People i have a excel spread sheet with many sheets inside it one for each day dating back 3 months sheets are named like 1st july05 till 1st oct05 1 for each day and blank sheets for weekends i want to add the totals of field b25 on all sheets onto a new sheet named totals can this be done? Mr. Banner, =SUM('First Sheet:Last Sheet'!B25) -- Earl Kiosterud www.smokeylake.com "Mrbanner" <mrbanner@swiftdsl.com.au> wrote in message news:1129255291.945224.222600@f14g2000cwb.googlegroups.com... > People i have a excel spread sheet with many sheets inside it one for ...

A one page report that prints an additional blank page
Hi I have a report that is 1 page and this is how it displays in print preview, however when I print I get a get the report and a blank page! Any suggestions on why this is. (I have not set the database up so it is possible that it may be set like this - if so how do I undo this). Hope you can help! Many thanks Emma -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200708/1 On Tue, 21 Aug 2007 17:39:22 GMT, Emlou85 via AccessMonster.com wrote: > Hi I have a report that is 1 page and this is how it displays in print > preview, ho...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

One graphic in my document prints as a black box..?
My publisher document contains numerous pictures/graphics but one picture prints as a black box from my HP Photosmart 8750. It prints fine on my laserjet printers and looks perfect in print preview. Is this a printer issue or a Publisher issue? Can anyone help? You might try changing the graphic file type, i.e., change from a jpg to a gif. This can be easily done with Irvanview. http://www.irfanview.com/ -- Don - Publisher 2000� Vancouver, USA "kellypendergrass" <kellypendergrass@discussions.microsoft.com> wrote in message news:77E1BDB8-32A0-4C72-A...