Lookup based on list selection in cell

[lukestkd]

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:

=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

[CheshireCat]

Hi Luke,

Can you attach a sample copy of your workbook?

[lukestkd]

GCSE Marks v18 - with Print Area.xlsmGCSE Marks v18 - with Print Area.xlsm

Spreadsheet attached, I have formatted the column in Student Analysis yellow, with the list box pink - for easier identification.

Many Thanks

Luke

[tommyt61]

See if this works.

[CheshireCat]

Sorry for the delay, got sidetracked by work.

Paste this:

=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]

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]

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.