Add 1 to a cell value after dropdown list selection


I am entering student attendance data daily.

I have made a drop down list to select if a student is Present, Absent, Sick, Away.
I have columns Total Present, Total Absent, Total Sick, Total Away.

If, for example, I select "Present" from drop down list I need 1 to be added to the number in the Total Present cell.

Then the next day I want to be able to open the spreadsheet and select from the drop down list again and have 1 added to the total number in the relevent cell - so at the end of the term I can see total days student was present, sick etc.

I've attached a file - can anyone help?

Thanks in advance



Selected Answer

First, this is not a good way to keep attendance as it is very easy to accidentally add extra days or make the wrong selection; in other words, this method is quite error-prone.

That said, here is a macro that should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 And Target.Row >= 7 Then

    If Target.Value = "Present" Then

        Cells(Target.Row, 4).Value = Cells(Target.Row, 4).Value + 1

    ElseIf Target.Value = "Absent" Then

        Cells(Target.Row, 5).Value = Cells(Target.Row, 5).Value + 1

    ElseIf Target.Value = "Sick" Then

        Cells(Target.Row, 6).Value = Cells(Target.Row, 6).Value + 1

    ElseIf Target.Value = "Away" Then

        Cells(Target.Row, 7).Value = Cells(Target.Row, 7).Value + 1

    End If

End If

End Sub

You were on the right path but you needed to put the macro into the worksheet where the code will run; after that, I just used a few IF statements to make the necessary checks.



Thanks for the feedback. I've taken your advice regarding errors and now am entering a selection for each day, so someone cannot be counted twice etc.   Thanks for the code - a big help, really appreciate it!
josh0034 (rep: 2) Mar 16, '17 at 10:37 pm
Glad to help! :) If you need anything else feel free to come back and ask a new question!
don (rep: 1969) Mar 17, '17 at 1:09 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login