Adding cells from 2 sheets to a third cell without value in cell changing

[FDPetey]

Hello all, first time I've posted and asked for help. I'm trying to make a spreadsheet for end users where they can enter a value into a cell on sheet 1 and in sheet 2, the values are added together for review by admin. That part is simple and working however, the problem I'm looking to fix is that sheet 1 will have new values entered weekly and the value in sheet 2 will need to be added onto so a monthly and quarterly running total is produced where other formulas can be run against that number. I basically just can't figure out how to keep a running total on sheet 2 as weekly entries are made on sheet 1.

Hope I explained this well.

Thanks in advance for any help.

 

[My Aswer Is This]

First, I only provide answers using Vba. If you want a formula solution someone else will need to help you.

So are you saying if you start by entering 12 into Range("A1") on sheet named "Alpha" you want Range("A1") on sheet Named "Bravo" to be increased by 12.

Is that what you want?
If not, please explain more.

 

[FDPetey]

First, I only provide answers using Vba. If you want a formula solution someone else will need to help you.

So are you saying if you start by entering 12 into Range("A1") on sheet named "Alpha" you want Range("A1") on sheet Named "Bravo" to be increased by 12.

Is that what you want?
If not, please explain more.

That is exactly it. And I want it to stay 12 so when they open it up again and enter say 10, A1 in Bravo changes to 22.

 

[My Aswer Is This]

OK would you now give me the specific details.
Give me the name of each sheet and what are the Ranges.

Like Say Sheet name "Mike" is where I plan to enter the value in Range("G2") or whatever. and sheet Named "Carol" Range("J4") is where I want the added value entered.

 

[FDPetey]

OK would you now give me the specific details.
Give me the name of each sheet and what are the Ranges.

Like Say Sheet name "Mike" is where I plan to enter the value in Range("G2") or whatever. and sheet Named "Carol" Range("J4") is where I want the added value entered.

Okay. User entry sheet is named "Training Hour Entry" with values entered into Range(D3:D4). Summary sheet is named "Calculation Summary" where Range(L2) =SUM('Training Hour Entry'!D3+'Training Hour Entry'!D4) and Range(L3) is where I want the value from L2 to be added making a cumulative total.

 

[My Aswer Is This]

You earlier said:
That is exactly it. And I want it to stay 12 so when they open it up again and enter say 10, A1 in Bravo changes to 22.

But now you're giving me things like:
with values entered into Range(D3:D4)

That is not one range.

 

[FDPetey]

You earlier said:
That is exactly it. And I want it to stay 12 so when they open it up again and enter say 10, A1 in Bravo changes to 22.

But now you're giving me things like:
with values entered into Range(D3:D4)

That is not one range.

Sorry, that was typo I meant to change. It should say Range(D3).
What I was originally looking at was summing D3 and D4 but realized that's not what I meant to do because the summing was taking place in L2 of the second sheet.

I've been researching this quite heavily thinking this is a cell formatting function issue and was wondering if Vba is a way to get it to work. I don't really know much of anything about Vba.

 

[My Aswer Is This]

So, when you enter the value, you want into range("D3") where on sheet named Summary do you want this value added to?

 

[FDPetey]

So, when you enter the value, you want into range("D3") where on sheet named Summary do you want this value added to?

Range(L2) on the Summary sheet

 

[My Aswer Is This]

Try this:
This is a sheet change event script.
Right click on sheet named: "Training Hour Entry"
And post this code:
Now when you enter any number in Range ("D3")
This value will then be added to the Range("L2") on sheet named: "Summary"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Then
On Error GoTo M
Dim ans As Variant
ans = Sheets("Training Hour Entry").Range("D3").Value
Dim anns As Long
anns = Sheets("Summary").Range("L2").Value
Sheets("Summary").Range("L2").Value = anns + ans
End If
Exit Sub
M:
MsgBox "You entered " & ans & vbNewLine & "this is not a number" & vbNewLine & "Try again"
End Sub