Closed Thread
Results 1 to 10 of 10

adding numbers in a single cell as a running total in that cell ?

  1. #1
    Registered User
    Join Date
    01-28-2009
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    8

    adding numbers in a single cell as a running total in that cell ?

    Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say "3 i.e. 3 tires or cans of coke" and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance.


    James
    Last edited by jlg371; 01-28-2009 at 01:20 AM. Reason: I read the forum rules after the fact !

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,254

    Re: adding numbers in a single cell as a running total in that cell ?

    Hello James,

    Welcome to the Forum!

    This requires a few VBA worksheet event macros.
    Please Login or Register  to view this content.
    How to Save a Worksheet Event Macro
    1. Copy the macro using CTRL+C keys.
    2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
    3. Left Click on View Code in the pop up menu.
    4. Paste the macro code using CTRL+V
    5. Make any custom changes to the macro if needed at this time.
    6. Save the macro in your Workbook using CTRL+S
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    32,956

    Re: adding numbers in a single cell as a running total in that cell ?

    That's only possible with VBA. This code needs to code on a sheet, it will watch what is entered into A1 and add to it each time you make a change.

    Right-Click on Sheet name
    Choose VIEW CODE
    Paste in Code
    Alt-F11 to close VBEditor
    Save sheet
    Please Login or Register  to view this content.
    The colored part could be duplicated to set up a second cell to watch...perhaps A2. Just change the MyCell references to MyCell2 in the added section, and insert a Static MyCell2 reference at the top, too.

    Play around with it.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    32,956

    Re: adding numbers in a single cell as a running total in that cell ?

    Leith, what does the second formula do? I like yours way better than mine, so as I add your superior version to my .CLS options, I just want to make sure I fully understand it.

  5. #5
    Registered User
    Join Date
    01-28-2009
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: adding numbers in a single cell as a running total in that cell ?

    Thanks alot guys for the quick answers I really appreciate the help....I can grasp what your saying but im not sure where to make the changes in those macros. I have'nt had to use a macro for anything yet and know nothing about them. i am making a weekly list that includes 7 days and prices for paid line items each has a 25 cells in a colum and 1 colum for each day of the week. so the range of cells that need this macro to make it work for this sheet would be cells d3 to j3 all the way down to d28 to j28 and all of them inside that...... would i have to do that for every one of those boxes ? or would that macro work for the whole page? will I need to input header coordinates for each macro:? lol sorry.
    Last edited by jlg371; 01-30-2009 at 09:07 PM. Reason: forgot to thank those who tried to help

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,254

    Re: adding numbers in a single cell as a running total in that cell ?

    Hello JB,

    The Selection_Change() event records the cell's value before it is changed and saves it in the variable Prev. When the cell 's contents change, the Worksheet_Change() event fires. The current contents are then added to the previous value.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: adding numbers in a single cell as a running total in that cell ?


  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,254

    Re: adding numbers in a single cell as a running total in that cell ?

    Hello Martin,

    Thanks for sharing that. Interesting article on the approach. The Auto_Open method has been replaced by the Workbook_Open() event. Microsoft has not supported Auto_Open since the introduction of Excel 2000, and recommends users against using it.

  9. #9
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: adding numbers in a single cell as a running total in that cell ?

    The Auto_Open won't fire if the file is opened by automated means.

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: adding numbers in a single cell as a running total in that cell ?

    cheers lads/lassies ?
    i'm only a humble '97 user. please forgive me for being so old!

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1