Lookup based on list selection in cell

lukestkd

New member
Joined
Aug 1, 2012
Messages
3
Reaction score
0
Points
0
Hello all,

In sheet Student Analysis, I have a column (G8:G37) which looks up whether the cells I1:I33 (in Summary sheet) is lower than C4:C33 (in Master Sheet) and shows the value in A4:A33 (in Summary Sheet) if it is.

Essentially it looks up whether a students % mark is lower than their target, and shows their name if it is. If it is higher then it shows nothing ("").
Code I am using is:
Code:
=IF(Summary!$I4<Master!$C4,Summary!$A4)

What I want to do:
- Be able to select a topic (column in summary sheet)
- Show students with a mark lower than their target for that topic only

- I've created a list box in H2 which has the headings from the 'Summary' Sheet (I3:O3)
- I now need to filter (G8:G37) to only show the column based on the topic selected in H2.

Example, if Topic 3 is selected in the Student Analysis sheet, G8:G37 should show K4:K33 (from Summary sheet), if Topic 5 is selected G8:G37 should show M4:M33 (from Summary Sheet).

Cheers,

Luke
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
See if this works.
 

Attachments

  • Student_Marks.xlsm
    212.4 KB · Views: 13

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
Sorry for the delay, got sidetracked by work.

Paste this:

Code:
=IF(INDEX(Summary!$I$4:$O$33,ROW(A1),MATCH($H$2,Summary!$I$3:$O$3,0))<Master!$C4,Master!$A4,"")

in cell 'Student Analysis'!G8 and copy autofill down.
 

lukestkd

New member
Joined
Aug 1, 2012
Messages
3
Reaction score
0
Points
0
See if this works.

Hi Tommy,
Many thanks for your input on this - absolutely fantastic :) . It is correctly showing the % grades, is there a way for it to update the list of students based on the selection in the list?

Cheers, Luke
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Orange column is where you need to test autoupdate of students based on list selection.If this works then you could just cut and paste over yellow cells and delete orange column.
 

Attachments

  • Student_Marks_II.xlsm
    215.7 KB · Views: 16
Top