Use formulas to automatically create lists based on criteria

[danielstone121]

Hey gurus-

I will try to be brief and clear. I have a worksheet with student ids and names as well as quiz scores. I want to create another worksheet that will contain only a list of the students that score below 70% on each quiz. I need the list to update automatically as students retake the quiz. The FT tab looks something like this. I only did the first 4 quizzes.

A

B

C

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

6

Stud ID

First

Last

Q1

Q2

Q3

Q4

Q5

Q6

Q7

Q8

Q9

7

300

Joe

Shmoe

100

0

100

0

8

400

Handy

Man

90

ERROR

100

BLANK

9

500

Sam

Sclub

69

100

100

BLANK

10

600

Sue

Shechef

35

100

100

0

<tbody>
</tbody>

This is the worksheet that contains the quiz scores for each student for each quiz. What I want to do is have another worksheet (named Quiz Report) that looks like this, based on the quiz information from the FT tab:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Quiz1

Quiz 2

Quiz 3

Quiz 4

Quiz 5

Quiz 6

Quiz 7

Quiz 8

Quiz 9

2

First

Last

First

Last

First

Last

First

Last

First

Last

First

Last

First

Last

First

Last

First

Last

3

Sam

Sclub

Joe

Shmoe

Joe

Shmoe

4

Sue

Shechef

Handy

Man

Handy

Man

5

Sam

Sclub

6

Sue

Shechef

<tbody>
</tbody>

The lists on the Quiz Report should include the first and last names of all students who score below 70%, all blanks, and all errors--for all quizzes. Does that make sense? Is it doable?