Forum Discussion
excel
Hello all, hope any of you can help me with a small issue that i have.
I have cell A1 and A2 cell A1 has to add up numbers from cell A2 but cell A2 changes everyday and i want cell A1 to keep adding up from cell A2.
How do i do this?
Thank you for you're reply.
You mentioned "it has to happen on the current date" so the code uses the current date. If you want to use the date in B1 instead, change the code to
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Range("B3,B20,B37,B54,B71"), Target) Is Nothing Then If IsNumeric(Target.Value) Then Application.EnableEvents = False Set rng = Target.Offset(0, 2).Resize(12, 1).Find(What:=Format(Range("B1").Value, "mmmm"), LookAt:=xlWhole) rng.Offset(0, 1).Value = Target.Value + rng.Offset(0, 1).Value Application.EnableEvents = True End If End If End Sub
17 Replies
I don't recommend doing it like that:
- You won't know which numbers contributed to the sum in A1.
- It will be difficult to correct errors.
Instead, enter each new number in the cell below the previous one, i.e. in A2, A3, A4, ...
In A1, enter the formula =SUM(A2:A10000)
You'll have the up-to-date total AND all numbers that make up the total.
- Hello sir, thank you for you're reply and answer, but in the spreadsheet i dont want to show all the rows with numbers because it is on a daily base. Prefer to have a formula to keep adding cell A1
You cannot do that with a formula. It requires VBA.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the code window.
Close the Visual Basic Editor.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Code:Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A2"), Target) Is Nothing Then Application.EnableEvents = False Range("A1").Value = [=SUM(A1:A2)] Application.EnableEvents = True End If End Sub
