Displaying data from multiple fields based on certain criteria.

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
72
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 :-

TeacherNameGeographyHistoryEnglishScienceMaths
Mrs HStephenFailFailPassPassFail
Mr BRichardPassPassPassPassFail
Mrs CJohnFailFailFailFailFail
Mr WMartinPassPassFailPassPass

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 :-

NameGeographyPass or FailRemediation Actions
FailRevise 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.

NameGeographyPass or FailRemediation Actions
StephenGeographyFailRevise more

NameHistoryPass or FailRemediation Actions
StephenHistoryFailStudy Harder

NameMathsPass or FailRemediation Actions
StephenMathsFailRead more Books


Hope this makes sense.

Thanks
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
75
Office Version
365
Platform
Windows
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

Board Regular
Joined
Feb 17, 2018
Messages
72
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

Board Regular
Joined
Sep 23, 2011
Messages
75
Office Version
365
Platform
Windows
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

Board Regular
Joined
Feb 17, 2018
Messages
72
This is the dashboard :-

TeacherNameGeographyHistoryEnglishScienceMaths
Mrs HStephenFailFailPassPassFail
Mr BRichardPassPassPassPassFail
Mrs CJohnFailFailFailFailFail
Mr WMartinPassPassFailPassPass


This is the Geography tab:-
NameGeographyPass or FailRemediation Actions
Revise more


History Tab:-
NameHistoryPass or FailRemediation Actions
Try Harder


English Tab:-
NameEnglishPass or FailRemediation Actions
Read More


Science Tab:-
NameSciencePass or FailRemediation Actions
Be more practical


Maths Tab:-
NameMathsPass or FailRemediation 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:-

NameGeographyPass or FailRemediation Actions
StephenFailRevise more
NameHistoryPass or FailRemediation Actions
StephenFailTry Harder
NameMathsPass or FailRemediation Actions
StephenFailShow working out more
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
75
Office Version
365
Platform
Windows
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.


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

Best Regards
M. Yusuf
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,921
Office Version
2007
Platform
Windows
Put the following code in the events of your "dashboard" sheet.

VBA Code:
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

Board Regular
Joined
Feb 17, 2018
Messages
72
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.


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

Board Regular
Joined
Feb 17, 2018
Messages
72
Put the following code in the events of your "dashboard" sheet.

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,921
Office Version
2007
Platform
Windows
Explain with generic data the structure of your sheet.
Show an example of how you want the output.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,024
Messages
5,472,077
Members
406,799
Latest member
trappschuh

This Week's Hot Topics

Top