Hi
I have a large list of pupils effort grades within the subjects that they
are studying: the list of all the subjects (about 18) is in row 1, pupil
names in column 1. If John Smith is studying music, and is awarded a 'A',
how can I search the row for the 'A', and return the column heading (i.e.
Music)? I've tried lookups and match and offset, but to no avail. I'm
obviously missing something...

Thanks
Tubthumper

See:

http://www.cpearson.com/excel/TablesAndLookups.aspx

--
Gary''s Student - gsnu200781


"Tubthumper" wrote:

Hi
I have a large list of pupils effort grades within the subjects that they
are studying: the list of all the subjects (about 18) is in row 1, pupil
names in column 1. If John Smith is studying music, and is awarded a 'A',
how can I search the row for the 'A', and return the column heading (i.e.
Music)? I've tried lookups and match and offset, but to no avail. I'm
obviously missing something...

Thanks
Tubthumper

Thanks for the link - some useful tips there for me. However, because I
don't know which column has the 'grade', I can't get it to look across the
row, find the 'A', and then return the column heading for that 'A'.

Here's hoping!



"Gary''s Student" wrote:

See:

http://www.cpearson.com/excel/TablesAndLookups.aspx

--
Gary''s Student - gsnu200781


"Tubthumper" wrote:

Hi
I have a large list of pupils effort grades within the subjects that they
are studying: the list of all the subjects (about 18) is in row 1, pupil
names in column 1. If John Smith is studying music, and is awarded a 'A',
how can I search the row for the 'A', and return the column heading (i.e.
Music)? I've tried lookups and match and offset, but to no avail. I'm
obviously missing something...

Thanks
Tubthumper

Hi,
What if the student has more than 1 "A"?
Do you want a list of subjects for which he got an "A"?
Regards - Dave.

Actually, that's it exactly! A list of all the subject for all the
students...it sounds so easy!!

Thanks


"Dave" wrote:

Hi,
What if the student has more than 1 "A"?
Do you want a list of subjects for which he got an "A"?
Regards - Dave.

Hi,
Put this macro into a module and run it.
Answer the questions it asks.
Is the result roughly what you want?

Sub ListSubjects()
Dim Grade As String
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
A = InputBox("What is the Row Number of the first student?")
B = InputBox("What is the Column Number of the first Subject?" & Chr(10) &
"(A=1, B=2 etc)")
C = InputBox("What is the Row Number or your subject headers?")
D = InputBox("What is the Column Number of your student names?" & Chr(10) &
"(A=1, B=2 etc)")
E = InputBox("What column would you like the results put into?" & Chr(10) &
"(A=1, B=2 etc)")
Grade = InputBox("What grade do you want listed?")
Do Until Cells(A, D) = ""
For i = 1 To 18
If Cells(A, B) = Grade Then Cells(A, E) = Cells(A, 20) & " " & Cells(C, B)
B = B + 1
Next i
B = 2
A = A + 1
Loop
End Sub

You'll have to unwrap any lines that have been wrapped by this newsgroup
format.
Be sure to input Column Numbers when asked (1, 2, 3 etc - not A, B, C etc)
Regards - Dave.