• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add monthly total without creating a column for every day

jenden888

New Member
Hi Excel brains
icon_smile.gif


Ideally, I would like to just have a monthly column and each time I would type a number in it, it would add it to the total already there.
There is already so many columns of different totals.
Maybe in VBA it could add or subtract the input number to a variable that is the number already in the same cell?

PS I did not post in VBA because I wasn't sure it was the right solution!

Any suggestions are welcome!

Thanks
 
Hi Jenden, and welcome to the forum! :awesome:

So that you have a better audit trail of your numbers, it would be best practice to list all your inputs/raw data somewhere, and then have a formula add them all up in your "totals" column. For instance, you could have in one table, two columns calling out
Date | Value

Then, in your summary/totals table, you could do some SUMIF formulas that would add up all the values.
upload_2016-4-20_10-17-49.png
 
I wrote a little piece of VBA that allows this
Enter a value in the Yellow area and the new value is added to the old value in the same cell

see attached file:
 

Attachments

  • Accumulate incell.xlsm
    15 KB · Views: 6
Thanks guys, I really appreciate your answers. I will look into it and will let you know how it turned out! :)
 
Hui, that is exactly what I meant. Totally awesome :)
Now ... what if the user makes a mistake ... example: adds 3 instead of 1
Could the code capture a +/- and then add or subtract at will?
 
I have now added an Undo function as well
there is only 1 undo level
 

Attachments

  • Accumulate incell.xlsm
    17.5 KB · Views: 9
Hui, you are helpful beyond words ... a true MVP

What about the fact that the code ends when you click outside of the area with yellow cells? It may happen with a user and then they have to reload the file or they don't realize it and think the code works when in fact they write over the previous number.

Thanks again
 
I'm not sure what you mean by "the code ends when you click outside of the area with yellow cells?" ?

The code works all the time

But it only changes the cells which are shaded yellow, that is by design

I am sure there will be cells where you want people to enter other values and this allows you to control that behaviour simply by the cells color

You can change that to any color or other property eg: bold or Italic or Blue Text etc
 
Hui (or anybody else), How can I make this code applicable to the whole workbook instead of just sheet1? Thanks
 
Back
Top