Marks Sheet

[josephrozario]

<small style="box-sizing: border-box; -webkit-font-smoothing: antialiased; font-size: 11.04px; text-transform: uppercase; display: block; color: rgb(105, 109, 111); font-family: "Helvetica Neue", roboto, Arial, sans-serif; background-color: rgb(238, 242, 244); outline: none !important; -webkit-tap-highlight-color: transparent !important;">
</small>I Have put data validation of subjects in a cell, I need Highest Marks and Student Name to be populated when I select a particular subject.(and if more than one student have the same highest marks, than all the student names) The Subjects are in columns and Student Names are in rows.

[TABLE="width: 772"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]Class 10-D Final Exam Report Card[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roll No.[/TD]
[TD]Student Name[/TD]
[TD]English[/TD]
[TD]Hindi[/TD]
[TD]Maths[/TD]
[TD]Science[/TD]
[TD]Social Science[/TD]
[TD]TOTAL[/TD]
[TD]AGGREGATE%[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ajay Kumar[/TD]
[TD]83[/TD]
[TD]76[/TD]
[TD]90[/TD]
[TD]91[/TD]
[TD]89[/TD]
[TD]429[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mohan Singh[/TD]
[TD]89[/TD]
[TD]76[/TD]
[TD]88[/TD]
[TD]85[/TD]
[TD]80[/TD]
[TD]418[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Deepak Bansal[/TD]
[TD]81[/TD]
[TD]70[/TD]
[TD]79[/TD]
[TD]70[/TD]
[TD]77[/TD]
[TD]377[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Samir Sharma[/TD]
[TD]90[/TD]
[TD]78[/TD]
[TD]93[/TD]
[TD]91[/TD]
[TD]90[/TD]
[TD]442[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mohit Pandey[/TD]
[TD]88[/TD]
[TD]71[/TD]
[TD]85[/TD]
[TD]81[/TD]
[TD]73[/TD]
[TD]398[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Pankaj Mathur[/TD]
[TD]78[/TD]
[TD]72[/TD]
[TD]87[/TD]
[TD]88[/TD]
[TD]77[/TD]
[TD]402[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ashish Pandey[/TD]
[TD]94[/TD]
[TD]91[/TD]
[TD]98[/TD]
[TD]97[/TD]
[TD]90[/TD]
[TD]470[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Joseph Rozario[/TD]
[TD]93[/TD]
[TD]90[/TD]
[TD]99[/TD]
[TD]97[/TD]
[TD]88[/TD]
[TD]467[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Subject[/TD]
[TD]Highest Marks[/TD]
[TD]Student Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]English[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

This macro assumes that your data validation list is in cell B12 and Subject,Highest Marks and Student Name are in B11, C11 and D11 respectively. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in B12.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B12")) Is Nothing Then Exit Sub
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim maxVal As Range
    Dim sAddr As String
    Range("C12:D" & LastRow).ClearContents
    Set foundSub = Rows(1).Find(Target)
    If Not foundSub Is Nothing Then
        Target.Offset(0, 1) = WorksheetFunction.Max(Range(Cells(2, foundSub.Column), Cells(bottomA, foundSub.Column)))
        Set maxVal = Range(Cells(2, foundSub.Column), Cells(bottomA, foundSub.Column)).Find(Target.Offset(0, 1))
        If Not maxVal Is Nothing Then
            sAddr = maxVal.Address
            Do
                Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = Cells(maxVal.Row, 2)
                Set maxVal = Range(Cells(2, foundSub.Column), Cells(bottomA, foundSub.Column)).FindNext(maxVal)
            Loop While maxVal.Address <> sAddr
            sAddr = ""
        End If
    End If
End Sub