Is it possible to make a graph that gets the plot points from the active row? (referencing the active row)?

[Rnkhch]

Hello,

I have a table that consists of one student per row, and the scores for each student in the same row. I'm wondering if it's possible to make a line graph such that it displays scores in time for the student in the active row. That way, I can just make one graph and visualize any student's progress over time by clicking on any cell in their row (rather than making numerous graphs for all students or making one overcrowded graph for all students).

I suppose I would need a way to reference the active row in "Series values:" when I 'select data' for the graph.

Any thoughts/guidance would be highly appreciated.

Thank you!

 

[Worf]

Hello

Are you familiar with VBA? The code below automatically does it when you click a row on the data table.

' sheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As ChartObject, cr As Range, s As Series
Set cr = [a40].CurrentRegion                        ' data table
If Not Intersect(Target, cr) Is Nothing Then        ' inside table?
    Set c = Me.ChartObjects("chart 4")              ' a single chart
    Set s = c.Chart.SeriesCollection(1)             ' a single series
    s.Values = Me.Range(Cells(Target.Row, 2), _
    Cells(Target.Row, cr.Columns.Count))            ' Y values (scores)
    s.Name = Cells(Target.Row, 1)                   ' from column A
End If
End Sub

 

[Rnkhch]

Hi, thanks a lot! If possible can you provide a short description of how to implement this in my excel sheet, since I've never used VBA

My sheet has 433 rows each corresponding to a student, and column A is the name, and columns B to CN are scores.

Thanks a lot!

 

[Worf]

Hi


o Open Excel and press alt + F11 to go to the VBE.
o Right click the sheet name on the left pane and choose view code.
o Paste the code on the right pane.
o Create a chart for one student. Note that the code does not change the X values.
o Change the chart name on the code or rename your chart to “chart 4”.
o The code assumes cell A40 is part of the data table. I would use a real Excel table(ribbon>insert>table).
o Clicking any cell should automatically update the chart.
o If you need I can post a link to my test workbook.

 

[Rnkhch]

Thanks! can you please post the link for your test workbook? I keep getting the error "the item with the specified name wasn't found" and debugging highlights the chart name line. I tried both with renaming in the code and renaming my chart. I changed the name from Chart title in Design>Add chart element.

Thank you!

 

[Worf]

To rename a chart, go to Ribbon>page layout>selection pane.

https://www.dropbox.com/s/x8vdm53bmsksf9b/students.xlsm?dl=0

 

[Rnkhch]

Wow! It works!! This is amazing Thank you so much! This will save me so much work


There are a couple of additional things related to this which will be even more amazing if you can help me with.

If these are too complex and require too much work, don't worry about them. I can get by with the current graphing.​

First is whether I can place the graph in my "main" sheet which is linked to the "scores" sheet through the VLOOKUP function. So each row in the main sheet corresponds to the same student in the scores sheet.

And second is whether it would be possible to hold Control or Shift and select more than one student (row) and get a few lines in the graph (for comparison purposes).

Thanks a lot!

 

[Worf]

o The code below is for a chart located at the main sheet. It assumes that this sheet has student names on column A, at the same rows where they appear at the scores sheet. If your layout is not like that, please explain.
o Use the control key to select disjoint ranges and the chart will display accordingly.

' Main sheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As ChartObject, cr As Range, s As Series, _
ws As Worksheet, ch_r As Range, mult As Range
Set ws = Sheets("18scores")
Set cr = Me.[a1].CurrentRegion
Set ch_r = ws.[a1].CurrentRegion
If Not Intersect(Target, cr) Is Nothing Then        ' inside table?
    Set c = Me.ChartObjects("chart 2")              ' a single chart
    c.Chart.HasLegend = True
    Do Until c.Chart.SeriesCollection.Count = 0
        c.Chart.SeriesCollection(1).Delete
    Loop
    For Each mult In Target
        Set s = c.Chart.SeriesCollection.NewSeries
        s.XValues = ws.Range(ws.Cells(2, 2), ws.Cells(2, ch_r.Columns.Count))
        s.Values = ws.Range(ws.Cells(mult.Row, 2), _
        ws.Cells(mult.Row, ch_r.Columns.Count))     ' Y values (scores)
        s.Name = Me.Cells(mult.Row, 1)
    Next
End If
End Sub

 

[Rnkhch]

Wow! It works beautifully!!! You are the absolute number one

Thanks for all the work!

 

[Worf]

You are welcome and thanks for the feedback!