Forum Discussion
Excel formula help
Aimee_Anderson Wouldn't the attached work for you?
Thanks, but no. That is not what I am trying to do.
I work in a school. I have 600 students and each week I tally the 'gold tickets (an individual class reward system) they earn. There are roughly 10 weeks per term, and four terms per year. I want to be able to total the number of tickets earned by each student over the course of a term and/or the year without having to use a cell for each week for each student. The sheet would be huge and unmanageable if I did this.
I want to be able to enter the number of tickets earned each week into the same cell per student and have a formula add that number to the previous week's number.
- But you must already have a list of all students' tickets somewhere, don't you?
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.