Displaying data from multiple fields based on certain criteria.

[Graham C1600]

Hi,
I've been asked to try and source a solution to a problem we are facing. It's a bit complicated so hopefully this will explain my requirements.

I have an excel document with numerous tabs and it's to do with exam passes or fails. The first sheet (let's call it dashboard) looks a little like this :-

Teacher	Name	Geography	History	English	Science	Maths
Mrs H	Stephen	Fail	Fail	Pass	Pass	Fail
Mr B	Richard	Pass	Pass	Pass	Pass	Fail
Mrs C	John	Fail	Fail	Fail	Fail	Fail
Mr W	Martin	Pass	Pass	Fail	Pass	Pass

So basically this tells me what they have passed and what they have failed.

I then have a further 5 sheets names Geography, History etc... The Geography one look a little like this :-

Name	Geography	Pass or Fail	Remediation Actions
		Fail	Revise more

My ask is a follows, I want to be able to click on a specific name (say Stephen) and it then looks across to see what he has failed. Then open a new sheet and populate this new sheet with the tables from Geography, History and Maths sheets as these were the subjects he failed but populate the name with the same name clicked i.e Stephen. So hopefully if I clicked Stephen I would see a new sheet with 3 smaller tables on it showing which subjects he failed and remediation actions. The reason for this is so I can see all the info in one place to pass back to students. Hopefully looking like this.

Name	Geography	Pass or Fail	Remediation Actions
Stephen	Geography	Fail	Revise more

Name	History	Pass or Fail	Remediation Actions
Stephen	History	Fail	Study Harder

Name	Maths	Pass or Fail	Remediation Actions
Stephen	Maths	Fail	Read more Books

Hope this makes sense.

Thanks

 

[Graham C1600]

Try this

Considerations:
1. You manually create the "Output" sheet. The macro will clean the sheet and put the new information each time you double click.
2. Put the code on the sheet where you want the event.
3. You must have created the sheets "Failure 1" to "Failure 6"
4. The columns to validate are: "W", "X", "Y", "Z", "AA", "AD"
5. You must double click on any cell in column "B".

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column <> 2 Then Exit Sub
  If Target.Value = "" Or Target.Row < 3 Then Exit Sub

  Dim sh2 As Worksheet, i As Long, arr As Variant
  Set sh2 = Sheets("Output")
  sh2.Cells.Clear
  arr = Array("W", "X", "Y", "Z", "AA", "AD")
  Range("A2:K2, A" & Target.Row & ":K" & Target.Row).Copy sh2.Range("A2")
 
  For i = 0 To UBound(arr)
    If Range(arr(i) & Target.Row) = "Y" Then
      With Sheets("Failure " & i + 1)
        .Range("A1", .Range("F" & Rows.Count).End(3)).Copy sh2.Range("A" & Rows.Count).End(3)(3)
      End With
    End If
  Next
 
  Cancel = True
  sh2.Select
End Sub

Hi Dante, this works a treat. Quick query though.

Is it possible to get the data from the dashboard (Heading 1 - Heading 11) to display on the output along with the data that it is pulling ? So it would look something like this :-

Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Heading 6	Heading 7	Heading 8	Heading 9	Heading 10	Heading 11
345345625	C-1505	Bakers	Mark D	Batch 191	17/09/2020	06/07/2020	73	FAIL	1	345345625
Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 23	Has it	Yes	Try again	Not known	Plan again
Heading 23	Had it	No	Leave it	Simple	Leave

Instead of just displaying the pulled data ?

Thanks for your support.

Graham

 

[DanteAmor]

Is it possible to get the data from the dashboard (Heading 1 - Heading 11)

In which row are the headings?

 

[Graham C1600]

In which row are the headings?

Row 5

 

[DanteAmor]

Change this line

Range("A2:K2, A" & Target.Row & ":K" & Target.Row).Copy sh2.Range("A2")

For this:

Range("A5:K5, A" & Target.Row & ":K" & Target.Row).Copy sh2.Range("A2")