Displaying data from multiple fields based on certain criteria.

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
96
Office Version
  1. 365
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Explain with generic data the structure of your sheet.
Show an example of how you want the output.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top