Forum Discussion
Excel formula help
Yes, each week every teacher sends a paper copy of their grade's tally. It is then compiled onto the digital copy where each student's ticket total is combined with the previous week for that student. This total then goes towards the house points. There are 28 grades each week.
I don't want to change the layout of the way the data is displayed. I just want to know if it is possible to create a formula or something where I can input the new week's numbers for that student and have it then add to their current total.
Aimee_Anderson Sure, with a bit of VBA this is easy enough. RIght-click on the sheet's tabe and choose "View Code". Paste this code into the window that opens:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub If Target.Cells.Count > 1 Then Exit Sub Application.EnableEvents = False Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + Target.Value Application.EnableEvents = True End SubNote that I have made it so that the adding of column A to column B only takes place if you enter a number into one cell at the time. This is controlled by the line of code that starts with
If Target.Cells.Count.
Thank you. It's still not working. I think this is because column A has student names in it. The first column that I enter data into is column E.
Do I need to have (and I'm assuming I do) a second column for the totals to go into?
I took it you were entering the numbers into column A as your first post mentions. Change the A:A in the code to whatever your column is (for instance E:E). The code currently assumes the total is one column to the right of the one you are entering the numbers into. If that needs to be more, adjust the Offset(0, 1) accordingly.