Return column heading

[Tubthumper]

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

 

[Gary''s Student]

See:

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

 

[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!

 

[Dave]

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.

 

[Tubthumper]

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

Thanks

 

[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.