Oct 28 2021 08:01 AM
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.
Oct 28 2021 08:11 AM
I don't recommend doing it like that:
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.
Oct 28 2021 08:44 AM
Oct 28 2021 08:49 AM
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
Oct 28 2021 09:17 AM
Oct 28 2021 10:57 AM
Oct 28 2021 12:14 PM
Should this depend on the current date, or on the date in B1, B23, B44 etc.?
Oct 28 2021 12:20 PM
Oct 28 2021 12:54 PM
See the attached version. I made the layout a bit more consistent.
Oct 28 2021 01:22 PM
Oct 28 2021 02:50 PM
SolutionYou 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
Oct 28 2021 04:08 PM
Thank thank you very much, I really appreciate that you have help me with this issue.
Dec 14 2021 11:32 PM
Oct 28 2021 02:50 PM
SolutionYou 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