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

 

[mamady]

Hi Graham,

There are multiple ways to achieve this.
Nonetheless, in order to advise on the most efficient method, it would better if you could share a sample workbook.

Best Regards
M. Yusuf

 

[Graham C1600]

Hi Graham,

There are multiple ways to achieve this.
Nonetheless, in order to advise on the most efficient method, it would better if you could share a sample workbook.

Best Regards
M. Yusuf

Thanks,

Might be a silly question but how do I add a sample file ? I cannot see any options to do this.

 

[mamady]

Hi Graham,

You can upload a file on an online sharedrive (i.e. dropbox, google drive, sharepoint ... etc) and share me the link to download.

Alternatively, you may follow the video tutorial available on the below link
XL2BB - Excel Range to BBCode

Regards
M. Yusuf

 

[Graham C1600]

This is the dashboard :-

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

This is the Geography tab:-

Name	Geography	Pass or Fail	Remediation Actions
			Revise more

History Tab:-

Name	History	Pass or Fail	Remediation Actions
			Try Harder

English Tab:-

Name	English	Pass or Fail	Remediation Actions
			Read More

Science Tab:-

Name	Science	Pass or Fail	Remediation Actions
			Be more practical

Maths Tab:-

Name	Maths	Pass or Fail	Remediation Actions
			Show working out more

My ask is a follows, I want to be able to click on a specific name (say Stephen on the dashboard tab) 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 etc 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. Look something like this:-

Name	Geography	Pass or Fail	Remediation Actions
Stephen		Fail	Revise more
Name	History	Pass or Fail	Remediation Actions
Stephen		Fail	Try Harder
Name	Maths	Pass or Fail	Remediation Actions
Stephen		Fail	Show working out more

 

[mamady]

Hi Graham,

To archive the double click method, it would require VBA coding, which unfortunately I am not an expert in such.

I recommend avoiding complicating the worksheet with macros. Therefore, I created a section on the dashboard, where you can select the student name from a drop list and all details will be shown

I uploaded the sample sheet on the following link. You can check if this satisfies your requirement.

Displaying data.xlsx

drive.google.com

If yes, I can walk you through how to expand on the current selection.

Best Regards
M. Yusuf

 

[DanteAmor]

Put the following code in the events of your "dashboard" sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 2 Then
    If Target.Value = "" Or Target.Row = 1 Then Exit Sub
    
    Dim i As Long, j As Long, k As Long
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim f As Range, sActs As Variant
    
    Set sh1 = ActiveSheet
    Set f = Rows(Target.Row).Find("Fail", , xlValues, xlWhole)
    If f Is Nothing Then
      MsgBox "No data with Fail"
      Exit Sub
    End If
    
    sActs = Array("Revise more", "Try Harder", "Show working out more", "etc")
    Set sh2 = Sheets.Add(after:=Sheets(Sheets.Count))
    k = 0
    
    For i = 3 To sh1.Cells(1, Columns.Count).End(1).Column
      If LCase(sh1.Cells(Target.Row, i)) = LCase("Fail") Then
        j = j + 1
        sh2.Range("A" & j).Resize(1, 4).Value = Array("Name", sh1.Cells(1, i).Value, "Pass or Fail", "Remediation Actions")
        j = j + 1
        sh2.Range("A" & j).Resize(1, 4).Value = Array(sh1.Cells(Target.Row, 2).Value, "", "Fail", sActs(k))
        j = j + 1
        k = k + 1
        If k > UBound(sActs) Then k = 0
      End If
    Next
  End If
  Cancel = True
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

It works in the following way.
Double-click a name in column B.
The macro creates a new sheet at the end of your sheets and creates the small tables.

In this line of the macro you can write the different actions.
sActs = Array("Revise more", "Try Harder", "Show working out more", "etc")

 

[Graham C1600]

Hi Graham,

To archive the double click method, it would require VBA coding, which unfortunately I am not an expert in such.

I recommend avoiding complicating the worksheet with macros. Therefore, I created a section on the dashboard, where you can select the student name from a drop list and all details will be shown

I uploaded the sample sheet on the following link. You can check if this satisfies your requirement.

Displaying data.xlsx

drive.google.com

If yes, I can walk you through how to expand on the current selection.

Best Regards
M. Yusuf

Thanks for the assistance. I think following this method would take too much work but appreciate your help.

 

[Graham C1600]

Put the following code in the events of your "dashboard" sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 2 Then
    If Target.Value = "" Or Target.Row = 1 Then Exit Sub
   
    Dim i As Long, j As Long, k As Long
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim f As Range, sActs As Variant
   
    Set sh1 = ActiveSheet
    Set f = Rows(Target.Row).Find("Fail", , xlValues, xlWhole)
    If f Is Nothing Then
      MsgBox "No data with Fail"
      Exit Sub
    End If
   
    sActs = Array("Revise more", "Try Harder", "Show working out more", "etc")
    Set sh2 = Sheets.Add(after:=Sheets(Sheets.Count))
    k = 0
   
    For i = 3 To sh1.Cells(1, Columns.Count).End(1).Column
      If LCase(sh1.Cells(Target.Row, i)) = LCase("Fail") Then
        j = j + 1
        sh2.Range("A" & j).Resize(1, 4).Value = Array("Name", sh1.Cells(1, i).Value, "Pass or Fail", "Remediation Actions")
        j = j + 1
        sh2.Range("A" & j).Resize(1, 4).Value = Array(sh1.Cells(Target.Row, 2).Value, "", "Fail", sActs(k))
        j = j + 1
        k = k + 1
        If k > UBound(sActs) Then k = 0
      End If
    Next
  End If
  Cancel = True
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

It works in the following way.
Double-click a name in column B.
The macro creates a new sheet at the end of your sheets and creates the small tables.

In this line of the macro you can write the different actions.
sActs = Array("Revise more", "Try Harder", "Show working out more", "etc")

Hi Dante,

Thanks for the reply, this will work for my requirements. However I have not been fully honest with the task required. I put a simple spreadsheet on to see if was achievable first. Now it is I need to try and put it into my larger and sensitive data spreadsheet. Principal will be the same where we click a name in a column. It will still have columns to the right showing a pass or fail and will still have other tabs showing the remediation actions required for the failures. Is it possible to try and explain your code so I could adapt it for my spreadsheet or do you want me to send over a new sheet with all columns on but generic names such as column 1 etc ?? It's sensitive data so cannot show you it directly. Can do either. Thanks

 

[DanteAmor]

Explain with generic data the structure of your sheet.
Show an example of how you want the output.