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]

My script assumes the sheet is named "Summary."
I asked for the name of the sheet, and you said:
Range(L2) on the Summary sheet.

I know your new to this forum but since things need to be exact, I suggest when supplying sheet names, you may want to say sheet named "Summary"
And put the name in quotes.

 

[FDPetey]

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

Awesome. This works. I wound up having to play with a couple of range cell changes to get it to work the way I wanted it to but it's magnificent. Thank you. Additional question: I'm currently building this as one sheet per person where they would find their name on the tab and make the entry however, being more efficient, I could put everyone on the same sheet with summary calculations on the second sheet but how would that change the code above? Say if I had 50 people on a sheet 1, would I have to copy and paste the code above 50 times and just change the range cell for each one?

 

[My Aswer Is This]

I always think it's best to have as much data as possible on one sheet.
So lets just say you have two employees on the same sheet.
Tell me in what cell would you enter the new value in and and where would this value be entered on another sheet.
You would not need to do this.
would I have to copy and paste the code above 50 times.

We would do something like this.
If target.addrees ="$A$1" then do this
If target.addrees ="$A$2" then do this

so, give me two examples:
Show me the code you're using now after you modified the one, I sent you.
were the sheet names wrong or what.
This should be easy I can provide code for 2 and then you try modifying code and add a third one yourself, so I do not need to do all 50.
So lets go with the ideal of having all employees on one sheet

 

[My Aswer Is This]

Having some logical references would make things easier if possible.
But not absolutely needed.


Like
A1
A2
A3
Not
G4
F7
D5
Take care. I like helping.

 

[FDPetey]

Having some logical references would make things easier if possible.
But not absolutely needed.


Like
A1
A2
A3
Not
G4
F7
D5
Take care. I like helping.

 

[FDPetey]

So the only thing I added for the first sheet name was the word "sheet". You can see below it now says "Training Hour Entry Sheet". LOL. Not much of a thing there except for Target.Address = $E$3 with Range(E3).

In the Summary sheet, I deleted some columns for simplicity and to clean it up further changing the target from Range(L2) to Range(H2). Again, not much.

The major thing I changed/added was separating the target.value entry. Originally, the end user would make a single entry into Range(E3) which would be an entry of combined hours. Again, as I said yesterday, it worked perfectly. However, after I showed my boss, he stated there are 2 categories that need to be entered and tracked separately. He asked me if there was anyway I could separate the entries but still add them to the total. So, I worked with the code and through trial and error, came up with this:

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

Basically, all I did was copy and paste the correct portion fixing the new target.address = "$F$3" and Range("F3"). I say "trial and error" because I didn't know exactly which portion needed to be copied so I kept at it until I figured it out. Testing of the end user side seems to be working without issue.

As I mentioned, now I'm looking at whether or not there is a way to add to this code so I could put all employees on Sheet("Training Hour Entry Sheet") instead of having each employee with their own sheet. There's actually not 50 employees, there is 182 but I was going to make 200 leaving room for expansion. This way, instead of having 201 sheets respectively - 200 of Sheet("Training Hour Entry Sheet - Employee 'Name'") plus 1 Sheet("Summary"), I would only have 1 Sheet("Training Hour Entry Sheet") and 1 Sheet("Summary"). I believe it's much more efficient as it appears you agree per your comment.

So in a nut shell, I will have 200 employees on Sheet("Training Hour Entry Sheet"), each with two entries they will make inline with their name. Column E will be "New FR1 Hours" and Column F will be New ESO Hours. On Sheet("Summary"), each of the same employees will be listed with their own running totals being added just as before. As I stated in my reply yesterday, I didn't know if there was a simpler way to do this in the code versus having to copy and paste the operation 200 times changing the Target.Address and Ranges 200 times.

By the way, on a second note, thank you again for the help with this. I know this is probably simple for you but I am learning and getting very interested in wanting to learn more about Vba code. I wish I could've been taught some of these things earlier in my career because it would've made life simpler for me instead of just accepting that I couldn't make certain things work. Excel is such a powerful tool and I really enjoy working with it. I appreciate all I am learning from you.

 

[FDPetey]

Hope this helps. These are the exact Ranges I am using. :

Sheet("Training Hour Entry Sheet")

	A​	B​	C​	D​	E​	F​
1​	Hours Eligible	Last Name	First Name	Employee #	New FR1 Hours	New ESO Hours
2​	Y​	Employee	1	123	100	100
3​	Y​	Employee	2	234
4​	Y​	Employee	3	345
5​	Y​	Employee	4	456

Sheet("Summary")

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Incentive Pay Elegible	Attendee Last Name	Attendee First Name	Attendee Agency Personnel ID	Elite Credit Hrs.	New FR1 Hrs.	New ESO Hrs.	Total Credit Hrs.
2​	Y​	Employee	1	123	4,347.75	100​	100​	4,547.75
3​	Y​	Employee	2	234	7,050.75	0​	0​	7,050.75
4​	Y​	Employee	3	345	8,030.25	0​	0​	8,030.25
5​	Y​	Employee	4	456	6,425.25	0​	0​	6,425.25

If Employee 1 enters value into Sheet("Training Hour Entry Sheet") Range(E2) and Range (F2), both values are reflected in Range(F2) and Range(G2) respectively on Sheet("Summary") Range(F2) - (that's the easy part) - and added as running total to Range(H2). Employee 2 enters hours into Range(E3) and Range(F3) which is reflected in it's respective targets on Sheet("Summary") and so on.... I'm pretty sure you get the picture.

 

[My Aswer Is This]

You in your first post said:
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

Sounds like a script needs to run when a user enters a value into a cell.

Can we not have a script run when you click a button and do all 50 at the same time. Does the script actually need to run any time a certain cell value changes.

Writing a script to do it this way 50 times would require a lot of code.

I could write a script to run down column F and transfer these value to sheet named Summary. Doing all 50 0r whatever values are in column E.

This would only take one small script.

 

[FDPetey]

You in your first post said:
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

Sounds like a script needs to run when a user enters a value into a cell.

Can we not have a script run when you click a button and do all 50 at the same time. Does the script actually need to run any time a certain cell value changes.

Writing a script to do it this way 50 times would require a lot of code.

I could write a script to run down column F and transfer these value to sheet named Summary. Doing all 50 0r whatever values are in column E.

This would only take one small script.

The single script would work vs. writing one for each. That way, whether 1 employees entry is made or multiple employee entries are made at the same time on Sheet("Training Hour Entry Sheet"), they would all update on Sheet("Summary") when a button is clicked to update. I was just thinking that adding values (hours) on sheet 1 had to be updated in real time on sheet 2 but it sounds easier and more efficient to write a single script since I am wanting to change it by putting all employees on one sheet instead of having 1 sheet for each employee. Then, using a button to update would also be good way for the user to double check their entries prior to the updating of cumulative hours on Sheet("Summary") and any mistakes can be caught by the employee before submitting the values where the total values on Sheet("Summary") could get messed up and have to be recalculated using historical data.

 

[My Aswer Is This]

So, the value in sheet Summary range F2 and G2 would always need to added to correct?
So in your example if shows 100 so next time you run script that value would increase in value from 100 to whatever it may be in the other sheet next time it was run. That other sheet name is way too long for me to remember without looking back. and I assumed the value in column H gets it's results from a formula in that cell.