SOLVED

excel

Copper Contributor

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.

17 Replies

@karlito2605 

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

@karlito2605 

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
Thank you very much.
Hello sir, can I send you the spreadsheet that i am working on so you have a understanding what i mean. Because it is not working for me.

@karlito2605 

Can you explain in detail what you want to do?

Ok let me try to explain, as you see the spreadsheet in colom B4 has a value that is going to change everyday, that value needs to be added in colom E13 as you see i am doing it on a monthly basis means that for every month at the end of the month i will have a value that sums up every days value.
If there was a possibility for me to get that so when the month changes it would go there also. for example we are in the month of October get the daily value for the month Oct. as we switch to November to get it in the November month.

@karlito2605 

Should this depend on the current date, or on the date in B1, B23, B44 etc.?

Yes, as you see there are 5 accounts and it has to happen on the current date

@karlito2605 

See the attached version. I made the layout a bit more consistent.

Yea sir I see what you mean, but what if my date in B1 changes? And i have to be in November. And then December?
Sorry the date is month/ day / year
best response confirmed by allyreckerman (Microsoft)
Solution

@karlito2605 

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

@Hans Vogelaar 

Thank thank you very much, I really appreciate that you have help me with this issue.

Can you help me in my issue too sir? My issue is "Excel cannot open the file (file.xlsx) because the file format or file extension is not valid. Verify that the file has been not corrupted and that the file extension matches the format of the file"

@Jayz0507 

That is completely unrelated to the subject of this discussion. Please start a new discussion and ask your question there.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@karlito2605 

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

View solution in original post