I have a list of numbers that is sumed to a sub total each calender day.

Presently, each day I manually add the new sub total for the day to the
master total for the month. I want the master total to retain the number in
the cell and automatically add the new sub total for each day.

I have created a table with all the days in the year and the following
formula generates the daily subtotal in a cell when the date on the spread
sheet = the day in the table.

=if(and($c$111=$A104,$e$111=D$78)personnel!$e$72,0 )

Where c111 = the day & e111 = the month.

The problem is that each new day all the formula recalculate and I lost the
number from the previous day.

How do I get the formula to only generate once? When the report is opened
for the next day the previous days subtotal is wiped so I can't used the
normal sum function.

or

How do I get the master total to accumulate each new day and ignore the zero
value that is now in the cell where the previous days subtotal was.

HAve spent two days to come up with the above formula and am completely
stumped.

HELP PLEASE!!

Hi,
You would have to adapt it to your needs :
Private Sub Worksheet_Change(ByVal Target As Range)
' stores old and new values for calculations
Dim OldVal As Variant, NewVal As Variant
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
'Your own calculation using NewVal + OldVal
Target.Value = NewVal
Application.EnableEvents = True
End Sub

HTH
Cheers
Carim

Thanks for taking the time to reply.

I am excel beginner - took my hours to nut out the formula in my post, so
need to ask - does the action below work in a macro (or something similar) or
does it somehow go directly into the cell as part of a formula?

If it goes into the cell do I start from the "if" below?

"Carim" wrote:

Hi,
You would have to adapt it to your needs :
Private Sub Worksheet_Change(ByVal Target As Range)
' stores old and new values for calculations
Dim OldVal As Variant, NewVal As Variant
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
'Your own calculation using NewVal + OldVal
Target.Value = NewVal
Application.EnableEvents = True
End Sub

HTH
Cheers
Carim

Hello Tracey,

No this is not a formula ...
It is an event macro, which means it is a macro that needs to be stored
in the module of the respective worksheet ...
Go to VBE with Alt F11 and copy into module of sheet you are working
on ...
But to make it work, you will have to fix the line ' Your own
calculation ...
Hope this is clear so far ...

HTH
Carim

Tracey,

To go a bit further, and help you write your calculation line :

1. What is the cell where you store what you call "New sub total for
the day" ?
2. What is the cell where you store what you call "Master total for the
month" ?
Are these inputs always located in a given cell or do they change
location ?

No doubt, you will fix this problem ...

Cheers
Carim

Hi Carim,

both totals are fixed in cells on the original spreadsheet "personnel"

new subtotal for the day is in cell E72

Total for the month is in cell E76

I have also created a table on the personnel sheet that has a cell for every
day of the year as a way to facilitate the date/total formula.

The month total also appears in another spreadsheet within the same workbook
where it is not fixed as a new total is put in for each month.

I'm very flexible to leave either out to make this work - whatever is easiest.

Thanks again

"Carim" wrote:

Tracey,

To go a bit further, and help you write your calculation line :

1. What is the cell where you store what you call "New sub total for
the day" ?
2. What is the cell where you store what you call "Master total for the
month" ?
Are these inputs always located in a given cell or do they change
location ?

No doubt, you will fix this problem ...

Cheers
Carim

Tracey,

It is up to you ...
There is no obligation, but if you want you can send me your worksheet
....
if you want me to fix it for you ...
my email is ( remove nospam in email )

Cheers
Carim

okay, that would great

"Carim" wrote:

Tracey,

It is up to you ...
There is no obligation, but if you want you can send me your worksheet
....
if you want me to fix it for you ...
my email is ( remove nospam in email )

Cheers
Carim