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

[jenden888]

Hi Excel brains

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

 

[Luke M]

Hi Jenden, and welcome to the forum!

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.

 

[Hui]

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:

 

[jenden888]

Thanks guys, I really appreciate your answers. I will look into it and will let you know how it turned out!

 

[jenden888]

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?

 

[jenden888]

I tried the undo but it did not work. Too bad, it would have been simpler!

 

[Hui]

I have now added an Undo function as well
there is only 1 undo level

 

[jenden888]

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

 

[Hui]

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

 

[jenden888]

Hui, you're right. It works as intended. Thanks again. Makes me feel like
I want to learn this stuff

 

[jenden888]

Hui, if I add data to the right, it goes under the UNDO button. How can I move it somewhere else?

 

[Hui]

The button is a shape
Right click it
Then drag it from the edge

 

[jenden888]

Thanks again Hui!!! Your follow-up is impeccable

 

[jenden888]

Hui (or anybody else), How can I make this code applicable to the whole workbook instead of just sheet1? Thanks