Search Gradebook for missing assignment

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a grade book in Excel and am entering the points for each
hw/quiz/test, but am also entering an "M" for a missing assignment or an "I"
for incomplete assignments. I would like to create a "Missing Work Report"
at the end of each week for the child to take home to be signed by the
parent. How do I look for the "M" & the "I" only for each child & put only
these rows into my new Excel report?
 
One way:

Assumng that you have headers in the table of hw/quiz/tests:
Highlight all the data
Select Data > Filter > AutoFilter
Click on the arrowhead of the Column with H & I in it and select Custom
In the "Show row where:" leave Equals in the left-hand box and enter H in
the right one
Select Or
Put Equals in the bottom left-hand box via the arrowhead and I in the right
one.
Click OK and print off the filtered list


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
An additional thought. If all the pupil's names are in the same table then
filter on the name before you filter by H or I

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanx...but this idea isn't going to work. What I'm doing is creating a
"Gradebook" for grades 6, 7 & 8 for 12 different teachers with little Excel
experience. To ask them to complete this task would be like asking them to
climb Mt. Rushmore! I'm attempting to create a completely separate "Missing
Work Report" for each of them to simply "run" that will "magically" pull each
child with a missing/incomplete assignment out & place it on its own report.

Any other ideas?
 
You could write a Macro to do the work automatically with something like
this:

Sub GradeBook()
If ActiveSheet.AutoFilterMode Then
Range("A3").AutoFilter
Exit Sub
End If

Dim LastRow As Long
Dim LastCol As Long
Dim LastGrade As Long
Dim GradeCol As Long
Dim pName As String
Dim NameCol As Long

LastRow = Cells(Rows.Count, 3).End(xlUp).Row
LastCol = Cells(3, Columns.Count).End(xlToLeft).Column
GradeCol = Application.Match("Grade", Rows("3:3"), False)
NameCol = Application.Match("Name", Rows("3:3"), False)

pName = InputBox("Please enter the Pupil's name", "Grade Book")


With Range(Cells(3, 1), Cells(LastRow, LastCol))
.AutoFilter Field:=NameCol, Criteria1:=pName
.AutoFilter Field:=4, Criteria1:="=H", Operator:=xlOr, _
Criteria2:="=I"
End With

End Sub

This assumes that the table starts with the labels in row 3 and has no other
data below in Column A or to the right of the table in Row 3 and all pupil's
names are in the same table, alter the code to suit if this is not correct.
The code will find the columns labelled Name and Grade itself.

You can create a shortcut key to run the Macro. Running the macro a second
time will unfilter the table. You may prefer to make the unfiltering part
as a different Macro so that the GradeBook macro can be run repeatedly to
show other pupil's results without having to unfilter the table.

Post back if you need further assistance.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top