Results 1 to 7 of 7

Thread: Lookup based on list selection in cell

  1. #1

    Lookup based on list selection in cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    Hi Luke,

    Can you attach a sample copy of your workbook?

  3. #3

    Spreadsheet

    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

  4. #4
    See if this works.
    Attached Files Attached Files

  5. #5
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    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.

  6. #6
    Quote Originally Posted by tommyt61 View Post
    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

  7. #7
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •